pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Albert REINER <areiner@tph.tuwien.ac.at>
To: pgsql-sql@postgresql.org
Subject: Re: fetching the id of a new row
Date: Fri, 9 Feb 2001 20:30:20 +0100
Message-ID: <20010209203020.A205@frithjof> (raw)
In-Reply-To: <Pine.NEB.4.10.10102081613240.3543-100000@what.openface.ca>
References: <Pine.NEB.4.10.10102081613240.3543-100000@what.openface.ca>

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



view thread (3+ messages)  latest in thread

Message-ID: <20010209203020.A205@frithjof>
Permalink:  ../20010209203020.A205@frithjof/
Also on:    postgresql.org/message-id/20010209203020.A205@frithjof

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: areiner@tph.tuwien.ac.at
  Subject: Re: fetching the id of a new row
  In-Reply-To: <20010209203020.A205@frithjof>
  References: <Pine.NEB.4.10.10102081613240.3543-100000@what.openface.ca> <20010209203020.A205@frithjof>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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