Re: Import ID column data then convert to SEQUENCE?

On 12/09/10 7:36 PM, gvim wrote:
> I'm migrating a database from MySQL and need to keep the original `id`
> column (AUTO_INCREMENT) intact so my plan is to create the `id`
> column, import the data then convert the new `id` column to a
> SEQUENCE. Is this possible/the best solution? Maybe a migration
> utility would be better? Suggestions?

that should work. except your terminology is slightly wrong. you
would crete a sequence, set its value to higher than the last, and
modify your ID to have a default value, like here is a normal serial...

$ psql
Welcome to psql 8.3.9, the PostgreSQL interactive terminal.

pierce=# create table stuff (id serial primary key, val text);
NOTICE: CREATE TABLE will create implicit sequence "stuff_id_seq"
for serial column "stuff.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"stuff_pkey" for table "stuff"
CREATE TABLE

pierce=# \d+ stuff
Table "public.stuff"
Column | Type |
Modifiers | Description
--------+---------+----------------------------------------------------+-------------
id | integer | not null default
nextval('stuff_id_seq'::regclass) |
val | text
| |
Indexes:
"stuff_pkey" PRIMARY KEY, btree (id)
Has OIDs: no

pierce=#

so.... create a similar table with an integer, populate it, and add
the sequence like...

pierce=# create table stuff2 (id integer primary key, val text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"stuff2_pkey" for table "stuff2"
CREATE TABLE

(prepopulate your table)

pierce=# create sequence stuff2_id_seq start 15432 owned by stuff2.id;
CREATE SEQUENCE

pierce=# alter table stuff2 alter column id set default
nextval('stuff2_id_seq'::regclass);
ALTER TABLE
pierce=#