[SQL] migrating numeric to serial from MSSQL to postgres
hi, am migrating a database from MSSQL to postgres. How would i migrate this: [Id] [numerc](18, 0) IDENTITY (1, 1) -- regards kg http://lawgon.livejournal.com http://nrcfosshelpline.in/web/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] migrating numeric to serial from MSSQL to postgres
I believe: IDENTITY(1, 1) just means "Primary Key" in M$SQL numeric 18,0 means a numeric field of zero decimal points. Hence we are looking at a 18 byte integer. bigint is not big enough, so probably should use the same in numeric 18,0 in postgres There may be a way to get MSSQL to dump a SQL compliant dump, which would make a migration to postgres much easier if your schema is large. Without a SQL compliant dump, you have a lot of cleaning up/tweaking the dump to make it readable by Postgres (but that's what I have done the few times in the past I have had to do that, fortunately not for many statements :) Terry Fielder [EMAIL PROTECTED] Associate Director Software Development and Deployment Great Gulf Homes / Ashton Woods Homes Fax: (416) 441-9085 Kenneth Gonsalves wrote: hi, am migrating a database from MSSQL to postgres. How would i migrate this: [Id] [numerc](18, 0) IDENTITY (1, 1) -- regards kg http://lawgon.livejournal.com http://nrcfosshelpline.in/web/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] migrating numeric to serial from MSSQL to postgres
> hi, > > am migrating a database from MSSQL to postgres. How would i migrate > this: > > [Id] [numerc](18, 0) IDENTITY (1, 1) > You might want to create a sequence first, such as with more or less options: CREATE SEQUENCE my_sequence INCREMENT BY 1 MINVALUE 1 NO MAXVALUE START WITH 1 CACHE 1 NO CYCLE; Then you should be able to migrate your code to something like: Id INTEGER NOT NULL DEFAULT NEXTVAL(my_sequence') -- Daniel ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] migrating numeric to serial from MSSQL to postgres
> I believe:
> IDENTITY(1, 1) just means "Primary Key" in M$SQL
>
IDENTITY is not obligatory a primary key. It's an auto-incremented column.
It might correspond to a PostgreSQL sequence.
[http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts
_ia-iz_3iex.asp]
> numeric 18,0 means a numeric field of zero decimal points. Hence we are
> looking at a 18 byte integer. bigint is not big enough, so probably
> should use the same in numeric 18,0 in postgres
>
numeric[ (precision[ , scale] )], where precision refers to the maximum
total number of decimal digits that can be stored. The default precision is
18, and for such a value the storage is of 9 bytes.
[http://msdn2.microsoft.com/en-us/library/ms187746.aspx]
Because it seems that a default value has been used here, I'd say that
Kenneth doesn't have any particular requirement, haven't you Kenneth?... :-)
An int or a bigint would be perhaps sufficient. My two cents.
I don't know whether Kenneth requires migrating database structure only or
whether he needs to migrate data too.
When migrating data, Kenneth might want to define a sequence which start
value begins at the next value of its IDENTITY column (cf.
IDENT_SEED(my_table)).
[http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts
_ia-iz_9yxw.asp]
Kenneth might want to transfer his data first and to define the sequence
after (for transfer performance reason?):
CREATE TABLE my_table
(
Id INTEGER NOT NULL,
...
);
[DTS process here]
CREATE SEQUENCE my_sequence
INCREMENT BY 1
MINVALUE 1
NO MAXVALUE
START WITH
CACHE 1
NO CYCLE;
ALTER TABLE m_table
ALTER COLUMN Id SET DEFAULT NEXTVAL('my_sequence');
Does that make sense?
--
Daniel
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL] Temp tables, reports in Postgresql (and other RDBMS)
Hi, We are considering moving some reports from *** to Postgres. Our reports are written as stored procs in Transact-SQL and usually have the following structure: CREATE PROCEDURE someReportProc AS /* Purpose: Creates a report based on Table1. * * Overview of what will be done: * 1) create a temp table based on Table1 (that has 3 columns) + 2 extra columns *(col4 and col5) and populate the temp table with data from Table1 * 2) run some logic to populate 1st extra column (col4) * 3) run some logic to populate 2nd extra column (col5) * 4) run select to return results to the client */ BEGIN -- step 1) create temp table #tempReportData SELECT Table1.*, space(1) as col4, 0 as col5 INTO #tempReportData FROM Table1 WHERE -- step 2) UPDATE #tempReportData SET col4 = Table4.someColumn FROM Table4 WHERE #tempReportData.id = Table4.id AND ... -- step 3) UPDATE #tempReportData SET col5 = Table5.someColumn + 123 FROM Table5 WHERE #tempReportData.id = Table5.id AND ... -- step 4) -- return data to the client, #tempReportData will be automatically dropped -- once this stored proc execution is completed SELECT * from #tempReportData END How would one rewrite the above logic in Postgresql? It should be noted that: 1) the real report logic may be much more complex. In other words, writing the report's logic with one SQL statement should be assumed impossible. 2) The reports are usually written to work on result sets, as in the example above. It's possible to rewrite the above logic with cursors, etc, though keeping the result set approach would be more preferable. Thanks in advance __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Temp tables, reports in Postgresql (and other RDBMS)
ow <[EMAIL PROTECTED]> writes: > We are considering moving some reports from *** to Postgres. Our reports are > written as stored procs in Transact-SQL and usually have the following > structure: > ... > How would one rewrite the above logic in Postgresql? If that's all you have to do, then SQL-language functions ought to be enough. If you need some procedural logic (if-then-else stuff) then you'd need to go over to plpgsql, which would be a bit of a pain because its habit of caching query plans doesn't play nice with temp tables. Possibly you could work some trick with ON COMMIT DELETE ROWS temp tables that are created once at the start of a session and are auto-emptied after each function by the ON COMMIT rule. Since the tables themselves don't get dropped, there's no problem with plan invalidation. There's also the possibility of using one of the other PLs such as plperl, if you're familiar with any of the common scripting languages they're based on. The other PLs don't do implicit plan caching so they won't have problems with temp tables; but it does mean knowing still another language and putting up with some notational inconvenience. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Temp tables, reports in Postgresql (and other RDBMS)
--- Tom Lane <[EMAIL PROTECTED]> wrote: > If you need some procedural logic (if-then-else stuff) Yes > then you'd need > to go over to plpgsql, which would be a bit of a pain because its habit > of caching query plans doesn't play nice with temp tables. Going to plpgsql is fine. What I'm trying to understand is how one goes about writing reports in Postgres as per our scenario, be that with temp tables, cursors, RECORDs, ROWTYPEs, etc. In our RDBMS we use temp tables because it's the easiest way to take some table as a base for your report and then extend it, as needed, with additional columns, as I showed in step (1). Also, once the stored proc has run, the temp table is automatically dropped, no need to worry about it. How does one go about in Postgres? Do people use cursors or whatever instead of temp tables? > could work some trick with ON COMMIT DELETE ROWS temp tables that are > created once at the start of a session and are auto-emptied after each > function by the ON COMMIT rule. Since the tables themselves don't > get dropped, there's no problem with plan invalidation. Not sure I understand. Our plan was actually to drop the temp table ON COMMIT, because it's stor proc that creates the temp table. If the session already has the temp table then creating it in stored proc again will fail, no? Thanks __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Assigning a timestamp without timezone to a timestamp
Did not seem to help: protocal2=> select start_datetime,cast(cast(cast(start_datetime as timestamp(0) without time zone) as varchar)||' '||B.timezone_ch as timestamp(0) with time zone) from reservation A, location B where appt_key = 7 and locn_key = 102 ; ERROR: invalid input syntax for type timestamp with time zone: "2006-07-13 09:20:00 America/New_York" Tom Lane-2 wrote: > > chrisj <[EMAIL PROTECTED]> writes: >> It works fine with fixed offset timezones, but when I try it with EST5EDT >> I get the following: >> ERROR: invalid input syntax for type timestamp with time zone: >> "2006-07-13 >> 09:20:00 EST5EDT" > > Try it with "America/New_York". The datetime parser seems to think that > a timezone name shouldn't contain digits ... which is bogus, but we'll > have to think carefully about how to improve it ... > > regards, tom lane > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly > > -- View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6827636 Sent from the PostgreSQL - sql mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Recursive pl/pgsql function ...
'k, this isn't quite working as expected (or, rather, hoped) ... I've created a
recursive function (that does work nicely) that takes a date in the past, and
keeps advancing in steps of 'n months' until the date is in the future:
CREATE OR REPLACE FUNCTION get_next_billing_date(timestamp with time zone,
int4) RETURNS TIMESTAMP AS '
DECLARE
max_date RECORD;
ret TIMESTAMP WITH TIME ZONE;
start_date ALIAS FOR $1;
payment_period ALIAS FOR $2;
BEGIN
SELECT INTO max_date CASE WHEN now() < start_date + ( payment_period || ''
months'')::interval THEN payment_period ELSE NULL END;
IF max_date.case IS NULL THEN
SELECT INTO ret get_next_billing_date(start_date + ( payment_period || ''
months'')::interval, payment_period);
ELSE
RETURN start_date + ( payment_period || '' months'')::interval;
END IF;
RETURN ret;
END;
' LANGUAGE plpgsql;
It works, no problem there:
# select get_next_billing_date('2005-10-15', 3);
get_next_billing_date
---
2007-01-15 00:00:00
(1 row)
But, as soon as I try and use that function as a field in a query, it gives an
error:
1 Record:
# select get_next_billing_date(activated, 12) from company_details where
company_id = 771;
get_next_billing_date
2007-05-03 15:09:19.491958
(1 row)
More then one Record:
# select get_next_billing_date(activated, 12) from company_details;
ERROR: control reached end of function without RETURN
CONTEXT: PL/pgSQL function "get_next_billing_date"
Something I've written wrong in the function, or just not something that is
doable?
Thanks ...
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED]
Yahoo . yscrappy Skype: hub.orgICQ . 7615664
pgpQYr5LP4REk.pgp
Description: PGP signature
Re: [SQL] Recursive pl/pgsql function ...
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > More then one Record: > # select get_next_billing_date(activated, 12) from company_details; > ERROR: control reached end of function without RETURN > CONTEXT: PL/pgSQL function "get_next_billing_date" Hm, what PG version is this? I couldn't duplicate that in HEAD, though I did find out that a zero or negative payment_period makes it recurse until "stack depth exceeded". regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Assigning a timestamp without timezone to a timestamp
chrisj <[EMAIL PROTECTED]> writes: > Did not seem to help: > ERROR: invalid input syntax for type timestamp with time zone: "2006-07-13 > 09:20:00 America/New_York" Sorry, I was thinking in terms of CVS HEAD ... the ability to use a full timezone spec in timestamptz input is new for 8.2. You might be able to use this, which does work in 8.1: select '2006-07-13 09:20:00'::timestamp at time zone 'EST5EDT'; regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Recursive pl/pgsql function ...
--On Sunday, October 15, 2006 23:27:34 -0400 Tom Lane <[EMAIL PROTECTED]> wrote:
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
More then one Record:
# select get_next_billing_date(activated, 12) from company_details;
ERROR: control reached end of function without RETURN
CONTEXT: PL/pgSQL function "get_next_billing_date"
Hm, what PG version is this? I couldn't duplicate that in HEAD, though
I did find out that a zero or negative payment_period makes it recurse
until "stack depth exceeded".
8.1.4 .. the odd thing is that I can throw any date at it, it seems, but I
can't use it as a 'field' in a query of a table, even when returning a single
record:
# select * from company_details where company_id = 76;
company_id | address_one | address_two | city| province_state |
postal_code | activated | disabled | recurring | referrer_info |
billing_type_id | currency_id | country_id
+-+-+---++-+-+--+---+---+-+-+
76 | 127 Main Street | | Wolfville | NS | B0P
1X0 | 2001-11-01 02:00:00 | | t | |
2 | 1 | 45
(1 row)
=# select get_next_billing_date('2001-11-01', 3);
get_next_billing_date
---
2006-11-01 00:00:00
(1 row)
=# select get_next_billing_date(activated, 3) from company_details where
company_id = 76;
ERROR: control reached end of function without RETURN
CONTEXT: PL/pgSQL function "get_next_billing_date"
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
=# \d company_details
Table "public.company_details"
Column |Type | Modifiers
-+-+---
company_id | integer |
address_one | text| not null
address_two | text|
city| text| not null
province_state | text|
postal_code | text|
activated | timestamp without time zone | default now()
disabled| timestamp without time zone |
recurring | boolean | default false
referrer_info | text|
billing_type_id | integer |
currency_id | integer |
country_id | integer |
Foreign-key constraints:
"company_details_billing_type_id_fkey" FOREIGN KEY (billing_type_id)
REFERENCES billing_type(id) ON UPDATE CASCADE ON DELETE SET NULL
"company_details_country_id_fkey" FOREIGN KEY (country_id) REFERENCES
country(id) ON UPDATE CASCADE ON DELETE SET NULL
"company_details_currency_id_fkey" FOREIGN KEY (currency_id) REFERENCES
cu
