Re: Serial field

Hi,

does anybody know, what happens, if a client-app. generates it's own id
like:

INSERT INTO orgs VALUES (
(SELECT max(id)+1 from org),
'orgname');

Will the next insert of just a name throw an error? (duplicate value on
primary key?)
I never tried that. But I do have a postgresql server accessed by two
clients. An old one (generating it's own id's) an a newer one (using DEFAULT
values). So I wrote my own function with pl/pgsql to generate my id's.
If I could handle the duplicate values with a sequenc, maybe it would be the
easier way.

kind regards,
Andre
----- Original Message -----
From: Jason Earl
To: webmaster(at)robbyslaughter(dot)com ; Francois Thomas ;
pgsql-novice(at)postgresql(dot)org
Sent: Friday, July 13, 2001 3:39 AM
Subject: RE: [NOVICE] Serial field
......
....Once you know how the
serial type actually works it becomes straightforward
to simply create the table like:

processdata=> CREATE TABLE orgs (id int primary key,
name char(10));
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit
index 'orgs_pkey' for table 'orgs'
CREATE

Once the table is created you can easily import your
data in whatever manner makes you the happiest. Once
your data is imported it is a simple manner to find
out the largest value for orgs.id (or whatever) with a
select statement like this:

SELECT max(id) FROM orgs;

Then create a new sequence with a start value one
higher than the value that is returned:

CREATE SEQUENCE orgs_id_seq START <value>;

Once you have got a sequence then you simply alter the
table so that it gets it's default values from that
sequence:

alter table orgs alter id set default
nextval('orgs_id_seq');

Presto, you have just created an auto increment field
from the ground up.

Hope this is helpful,
Jason