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