Thread: fetching the id of a new row

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

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@tph.tuwien.ac.at>
Deutsch       *       English       *       Esperanto       *       Latine
--------------------------------------------------------------------------

Albert REINER wrote:
> 
> 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

Actually nextval() works fine across backends. It always increments the
sequence, so repeated calls waste numbers.

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

Yep - it's either get nextval and insert or insert and check currval.

- Richard Huxton