Re: Looking for help regarding getting the latest inserted sequence value.

[Magnus Hagander <magnus(at)hagander(dot)net>]

Mavinakuli, Prasanna (STSD) wrote:
> Hello All,
> 
> We are looking for your help.The scenarion which we need to address
> is,There are 2 threads and both of them are in separate transction and
> insert the value to a same table and also sequence number field gets
> incremented automotically for each of them.The problem we are facing
> is,We will need to get back the appropriate id inserted for that
> particualr record as it is used in some other places.
> 
> Right now we are doing it in 2 steps.inserting the record to table.And
> getting the max(id) from the table.Now the problem is assume there is
> another thread also does the insertion and commits that transction both
> of the thread return the same id which is not desirable in our case.
> 
> It would be really very much helpful to know the form of a query which
> inserts record and also returns the latest inserted ID for that record
> in a single query.

If you're on 8.2 the easiest way is to use INSERT RETURNING. For example:
INSERT INTO t (somefield) VALUES ('somevalue') RETURNING pkey

with pkey being the SERIAL field.

You can also do it with currval() on the sequence, but that requires two
queries.

//Magnus