Re: SQL Help - Obtaining ID of record INSERTED

Thank you.

You stated that the SELECT currval('orderid_id_seq') statement is a
separate query and must be treated as such.

This gives me cause for concern. My intention is to obtain the orderid
of the order inserted at that moment, but if I am to use a separate
query it would seem there is a chance (albeit a small one) that another
order could come through before I am able to obtain the orderid.

Jason Minion wrote:

>Check out http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html
>
>You'll want to use something like "SELECT currval('<name of sequence>')". The thing
>is, your orderid field in orderstemp has a sequence which it uses to grab the
>next value. The currval function called with the name of the sequence as the
>parameter will give you results as you are accustomed to. However, it is a
>separate query and must be treated as such. Note that you need to call it
>using the same database connection. And sequences are special - they always
>update and do not roll back with failed transactions.
>
>If you used default serial notation in your create table statement, your table is
>likely named "orderid_id_seq":
>
>SELECT currval('orderid_id_seq'::text);
>
>Jason
>
>-----Original Message-----
>From: pgsql-admin-owner(at)postgresql(dot)org
>[mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of
>josh(at)segrestfarms(dot)com
>Sent: Tuesday, August 16, 2005 4:09 PM
>To: pgsql-admin(at)postgresql(dot)org
>Subject: [ADMIN] SQL Help - Obtaining ID of record INSERTED
>
>
>Greetings.
>
>INSERT INTO
> orderstemp (customerid,datecreated)
>VALUES
> ('5443','8/16/2005 12:00PM')
>
>The table orderstemp has a unique identifier field, orderid (of type
>SERIAL).
>
>How can I obtain the orderid of the record inserted in the INSERT INTO
>statement in postgresql?
>
>MSSQL does it like this:
>
>INSERT INTO
> orderstemp (customerid,datecreated)
>VALUES
> ('5443','8/16/2005 12:00PM')
>SELECT @@identity as orderid
>
>but this doens't work in postgresql.
>
>Any ideas? Thanks in advance.
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: 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
>
>
>
>