Re: SQL Help - Obtaining ID of record INSERTED

josh(at)segrestfarms(dot)com wrote:

> 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.

sequences are transaction/session safe thus it is not a problem. It
would be a problem if *you* in your session inserted another record
or if you disconnected before you ran the currval but as long as you are
in the same session you are fine.

Sincerely,

Joshua D. Drake

>
> 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
>>
>>
>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match