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 null default
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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly