Thread: 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/

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
>

> 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

> 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