Re: Import ID column data then convert to SEQUENCE?

[John R Pierce <pierce@xxxxxxxxxxxx>]

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




--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general