Skip site navigation (1) Skip section navigation (2)

Re: Serial field

From: A_Schnabel(at)t-online(dot)de (Andre Schnabel)
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Serial field
Date: 2001-07-13 05:45:26
Message-ID: 002d01c10b5f$04b5b360$0201a8c0@aschnabel.homeip.net (view raw or flat)
Thread:
Lists: 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(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




In response to

pgsql-novice by date

Next:From: Francois ThomasDate: 2001-07-13 09:00:15
Subject: TR: Serial field
Previous:From: Jason EarlDate: 2001-07-13 03:23:51
Subject: Re: Best formal training for PostgreSQL use?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group