Re: Getting ID of last-inserted row

On Wed, May 20, 2009 at 4:35 AM, David <wizzardx(at)gmail(dot)com> wrote:
>>> I never found an adequate (simple and efficient) method for getting
>>> the primary key ID of the just-inserted row, and usually used
>>> transactions and "select last value, ordered by id"-type queries to
>>> get the last id value, or other ugly logic.
>>
>> use currval() instead, see
>> http://www.postgresql.org/docs/current/static/functions-sequence.html
>> --
>
> I think I researched that before also, but I wasn't sure at the time
> how safe it was against race conditions. Although I see now (reading
> the docs again) that it is tracked for different sessions so it should
> be safe.

It is.

>> Also, you can do insert....returning... (as of version 8.2, I think):
>>
>> INSERT INTO clients (id, name)
>>  VALUES (nextval('clients_id_seq'), 'John Smith')
>>  RETURNING id;
>
> Thanks. I think I saw that too, not too long ago, but forgot about it.
> Some of the PostgreSQL services I use are on older versions, so I need
> to use older syntax. But, this will be useful when the db version is
> guaranteed to be recent. Thanks also for your reminder.

The one thing returning makes easy is getting the return id of multiple records.

insert into tablea values (val),(val),(val),(val) returning id;

will return a set of 4 different ids, not just one. And if there's a
gap in the sequence you'll not get the wrong numbers.