Re: Getting unique ID through SQL

Hi Patrick,

With PostgreSQL, I do this inside PL/PGSQL functions (but I'll do it
outside a function here to make it simpler) :

Lets say you have :

foobar=# create table demonstration (barfoo serial, data varchar(10));
NOTICE:  CREATE TABLE will create implicit sequence
'demonstration_barfoo_seq' for SERIAL column 'demonstration.barfoo'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index
'demonstration_barfoo_key' for table 'demonstration'
CREATE
foobar=# \d demonstration                               Table "demonstration"Attribute |    Type     |
       Modifier
 
-----------+-------------+------------------------------------------------------------barfoo    | integer     | not
nulldefault
 
nextval('demonstration_barfoo_seq'::text)data      | varchar(10) |
Index: demonstration_barfoo_key
foobar=#

The way I insert data in a scalable manner is :

foobar=# select nextval('demonstration_barfoo_seq');     /* Put this
returned value in a variable */nextval
---------      1
(1 row)
foobar=# insert into demonstration (barfoo, data) values (1, 'Some
data');  /* Insert the data using the previously generated serial number
*/ 
INSERT 28776302 1
foobar=#

Pretty simple eh?  No two clients can get the same value, and therefore
there's no conflict.  It's even transaction safe, as rolling back a
transaction won't let the same value be generated again.  This does mean
you will get gaps in the sequence numbering after a while, but for my
applications that's not a problem.

Regards and best wishes,

Justin Clift
Database Administrator


Patrick Dunford wrote:
> 
> People will have seen my post on problems with PostgreSQL ODBC driver and MS
> Access 97.
> 
> Access 97 has some problems when a record is added that contains a primary
> key field of type SERIAL. This has something to do with the fact that the
> value of the primary key is not actually generated until the record is sent
> to the server.
> 
> It seems it is easiest for me to get the unique ID from the server myself
> and insert it into the record when Access creates it.
> 
> In the realm of file based databases on a local machine it is easy to do
> this: store the unique variable into a special table, read it out, increment
> it and store it back. Very quick and there may only ever be one user.
> 
> Things become different on an SQL server because there may be multiple users
> simultaneously accessing the database. Two SQL operations are required to
> retrieve the variable's value and update it: a SELECT and UPDATE. Depending
> on how fast your connection is, between the SELECT and UPDATE, someone else
> could have run the same SELECT and got the same value back. Then when both
> records are sent to the server with duplicate values in the same primary
> key, one will fail.
> 
> What I need is some foolproof way of getting and updating the variable in
> one operation. Is it going to be an Int4 stored in a special table, or can
> it be a serial? Do I use a stored procedure or what? How do I get its value
> from Access?
> 
> Whatever you think of Access, the alternative seems to be clunky PHP forms
> with lots of code behind them for data entry and editing.
> 
> =======================================================================
> Patrick Dunford, Christchurch, NZ - http://pdunford.godzone.net.nz/
> 
>    Peter replied, ?Repent and be baptized, every one of you, in the
> name of Jesus Christ for the forgiveness of your sins. And you will
> receive the gift of the Holy Spirit.   The promise is for you and
> your children and for all who are far off-for all whom the Lord our
> God will call.?
>     -- Acts 2:38
> http://www.heartlight.org/cgi-shl/todaysverse.cgi?day=20010304
> =======================================================================
> Created by Mail2Sig - http://pdunford.godzone.net.nz/software/mail2sig/
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly