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=#