Re: Serial field - Mailing list pgsql-novice

From A_Schnabel@t-online.de (Andre Schnabel)
Subject Re: Serial field
Date
Msg-id 002d01c10b5f$04b5b360$0201a8c0@aschnabel.homeip.net
Whole thread Raw
In response to RE: Serial field  (Jason Earl <jdearl@yahoo.com>)
List pgsql-novice
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@robbyslaughter.com ; Francois Thomas ;
pgsql-novice@postgresql.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




pgsql-novice by date:

Previous
From: Jason Earl
Date:
Subject: Re: Best formal training for PostgreSQL use?
Next
From: Francois Thomas
Date:
Subject: TR: Serial field