Re: fetching the id of a new row

On Thu, Feb 08, 2001 at 05:28:59PM -0500, Jelle Ouwerkerk wrote:
> Hi,
>
> How might I insert a new row into a table and return the id of the new row
> all in the same SQL statement? The id is generated by a sequence. Up to
> now I've been getting the nextval of the sequence first and then inserting
> with the id in a second SQL exec. Is there a faster way (in a general
> case, without writing SQL or plpgsql functions)?
>
> Thanks

I do not know of a way to insert and select in one statement without
the use of a function (what's the problem with those, by the way?),
but as far as I can tell nextval() will return the next value for any
backend, so if you have more than one backend inserting at the same
time you might end up inserting with the same id twice. Instead you
should insert once, without specifying the id (so that the default
value, which must be set to nextval()) will be used; to obtain the id,
if indeed you need it, you can than select currval(), which is
guaranteed to work on a per-backend basis.

Albert.

--

--------------------------------------------------------------------------
Albert Reiner <areiner(at)tph(dot)tuwien(dot)ac(dot)at>
Deutsch * English * Esperanto * Latine
--------------------------------------------------------------------------