pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfetching the id of a new row
3+ messages / 3 participants
[nested] [flat]
* fetching the id of a new row
@ 2001-02-08 22:28 Jelle Ouwerkerk <jelle@openface.ca>
0 siblings, 1 reply; 3+ messages in thread
From: Jelle Ouwerkerk @ 2001-02-08 22:28 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: fetching the id of a new row
@ 2001-02-09 19:30 Albert REINER <areiner@tph.tuwien.ac.at>
parent: Jelle Ouwerkerk <jelle@openface.ca>
0 siblings, 1 reply; 3+ messages in thread
From: Albert REINER @ 2001-02-09 19:30 UTC (permalink / raw)
To: pgsql-sql
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
--------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: fetching the id of a new row
@ 2001-02-11 08:23 Richard Huxton <dev@archonet.com>
parent: Albert REINER <areiner@tph.tuwien.ac.at>
0 siblings, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2001-02-11 08:23 UTC (permalink / raw)
To: Albert REINER <areiner@tph.tuwien.ac.at>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-02-11 08:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-08 22:28 fetching the id of a new row Jelle Ouwerkerk <jelle@openface.ca>
2001-02-09 19:30 ` Albert REINER <areiner@tph.tuwien.ac.at>
2001-02-11 08:23 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox