From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | "Kevin B(dot)" <db(at)ke5in(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: sequence newbie question |
Date: | 2004-11-30 17:28:19 |
Message-ID: | 20041130172819.GA35188@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-11-30 17:44:57 | Re: sequence newbie question |
Previous Message | Tom Lane | 2004-11-30 17:18:56 | Re: views with parameters |