Re: Getting unique ID through SQL - Mailing list pgsql-hackers
From | Justin Clift |
---|---|
Subject | Re: Getting unique ID through SQL |
Date | |
Msg-id | 3AA36CCD.E3F64F61@bigpond.net.au Whole thread Raw |
In response to | Getting unique ID through SQL ("Patrick Dunford" <dunfordsoft@clear.net.nz>) |
List | pgsql-hackers |
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
pgsql-hackers by date: