Re: sequence newbie question

On Tue, Nov 30, 2004 at 10:57:23AM -0500, Kevin B. wrote:

> I'm coming over from the MS sql server world and I've been reading up on
> sequences. I have a question:
>
> I see that you can create an implicit sequence like this:
> CREATE TABLE test (field varchar(5), id serial NOT NULL, PRIMARY KEY (id));
>
> And I've read that you can replace the Microsoft @@Identity by using this:
> select last_value from test_id_seq;

Querying last_value returns the last value obtained by anybody, so
using it is subject to race conditions.  You probably want currval(),
which returns the last value obtained in the current session:

SELECT currval('test_id_seq');

If you need the value for subsequent inserts then you don't need
to SELECT it -- you can use currval() in the INSERT statements:

INSERT INTO product (prodname) VALUES ('Widget');
INSERT INTO sale (prodid, price) VALUES (currval('product_prodid_seq'), 9.95);

> But what if somebody else inserts a row after your insert but before your
> select? Will you end up with their ID or is the "last_value" somehow
> associated with the connection?

currval() is safe.  See the PostgreSQL documentation and FAQ:

http://www.postgresql.org/docs/7.4/static/functions-sequence.html
http://www.postgresql.org/docs/faqs/FAQ.html#4.15.3

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/