Re: Question about serial vs. int datatypes

Lynna Landstreet wrote:
> But... when converting an existing database that already has several hundred
> records in it, I can't make that field serial in PostgreSQL, can I? Because
> I don't want the existing records renumbered - that would break the links
> between the different tables. But if I make the id number just a smallint
> field, then I'm stuck incrementing it manually after the conversion. Is
> there any way around this? Any way to import the existing records with their
> id number intact, and then have it switch to serial after that for new
> records?

You can create the field as serial. A serial data type makes the column
*default* to the next value of a sequence, but if you provide a value
(as you would during the import), the default is not used. But you will
need to set the sequence after the import so that it starts with a
number higher than any you imported. See:

http://www.us.postgresql.org/postgresql-7.3.3/functions-sequence.html

For example:

regression=# create table s1(id serial primary key, f2 text);
NOTICE:  CREATE TABLE will create implicit sequence 's1_id_seq' for
SERIAL column 's1.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 's1_pkey'
for table 's1'
CREATE TABLE
regression=# \d s1
                            Table "public.s1"
  Column |  Type   |                     Modifiers
--------+---------+----------------------------------------------------
  id     | integer | not null default nextval('public.s1_id_seq'::text)
  f2     | text    |
Indexes:
     "s1_pkey" PRIMARY KEY btree (id)

INSERT INTO s1(id,f2) VALUES (1,'hello');
INSERT INTO s1(id,f2) VALUES (2,'world');
SELECT setval('s1_id_seq',42);
INSERT INTO s1(f2) VALUES ('abc');
regression=# SELECT * FROM s1;
  id |  f2
----+-------
   1 | hello
   2 | world
  43 | abc
(3 rows)

HTH,

Joe