From: Daniel CAUNE <d.caune@free.fr>
To: Terry Fielder <terry@ashtonwoodshomes.com>
To: Kenneth Gonsalves <lawgon@thenilgiris.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: migrating numeric to serial from MSSQL to postgres
Date: Sun, 15 Oct 2006 11:23:01 -0400
Message-ID: <001001c6f06d$cdd508b0$0b00a8c0@tedy> (raw)
In-Reply-To: <45323DA5.1080601@ashtonwoodshomes.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: d.caune@free.fr, terry@ashtonwoodshomes.com, lawgon@thenilgiris.com
Subject: Re: migrating numeric to serial from MSSQL to postgres
In-Reply-To: <001001c6f06d$cdd508b0$0b00a8c0@tedy>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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