pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmigrating numeric to serial from MSSQL to postgres
4+ messages / 3 participants
[nested] [flat]
* migrating numeric to serial from MSSQL to postgres
@ 2006-10-15 13:05 Kenneth Gonsalves <lawgon@thenilgiris.com>
0 siblings, 2 replies; 4+ messages in thread
From: Kenneth Gonsalves @ 2006-10-15 13:05 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: migrating numeric to serial from MSSQL to postgres
@ 2006-10-15 13:49 Daniel CAUNE <d.caune@free.fr>
parent: Kenneth Gonsalves <lawgon@thenilgiris.com>
1 sibling, 0 replies; 4+ messages in thread
From: Daniel CAUNE @ 2006-10-15 13:49 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: migrating numeric to serial from MSSQL to postgres
@ 2006-10-15 13:54 Terry Fielder <terry@ashtonwoodshomes.com>
parent: Kenneth Gonsalves <lawgon@thenilgiris.com>
1 sibling, 1 reply; 4+ messages in thread
From: Terry Fielder @ 2006-10-15 13:54 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: pgsql-sql
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
terry@greatgulfhomes.com
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: migrating numeric to serial from MSSQL to postgres
@ 2006-10-15 15:23 Daniel CAUNE <d.caune@free.fr>
parent: Terry Fielder <terry@ashtonwoodshomes.com>
0 siblings, 0 replies; 4+ messages in thread
From: Daniel CAUNE @ 2006-10-15 15:23 UTC (permalink / raw)
To: Terry Fielder <terry@ashtonwoodshomes.com>; Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: pgsql-sql
> 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 <IDENTITY current value + 1>
CACHE 1
NO CYCLE;
ALTER TABLE m_table
ALTER COLUMN Id SET DEFAULT NEXTVAL('my_sequence');
Does that make sense?
--
Daniel
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-10-15 15:23 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-15 13:05 migrating numeric to serial from MSSQL to postgres Kenneth Gonsalves <lawgon@thenilgiris.com>
2006-10-15 13:49 ` Daniel CAUNE <d.caune@free.fr>
2006-10-15 13:54 ` Terry Fielder <terry@ashtonwoodshomes.com>
2006-10-15 15:23 ` Daniel CAUNE <d.caune@free.fr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox