Skip site navigation (1) Skip section navigation (2)

Re: SQL Help - Obtaining ID of record INSERTED

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: "josh(at)segrestfarms(dot)com" <josh(at)segrestfarms(dot)com>
Cc: Jason Minion <jason(dot)minion(at)sigler(dot)com>,pgsql-admin(at)postgresql(dot)org
Subject: Re: SQL Help - Obtaining ID of record INSERTED
Date: 2005-08-19 05:08:28
Message-ID: (view raw or whole thread)
Lists: pgsql-admin
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.


Joshua D. Drake

> Jason Minion wrote:
>> Check out 
>> 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.
>>    orderstemp (customerid,datecreated)
>>    ('5443','8/16/2005 12:00PM')
>> The table orderstemp has a unique identifier field, orderid (of type 
>> How can I obtain the orderid of the record inserted in the INSERT 
>> INTO statement in postgresql?
>> MSSQL does it like this:
>>    orderstemp (customerid,datecreated)
>>    ('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

In response to

pgsql-admin by date

Next:From: Ramesh PVKDate: 2005-08-19 06:15:35
Subject: installation on postgresql on different port
Previous:From: Alvaro HerreraDate: 2005-08-19 03:13:39
Subject: Re: On-line backup and point-in-time recovery (PITR)

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group