Received: from www-uk-05.digiserve.com ([195.172.23.63]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f1BA9Dx61454 for ; Sun, 11 Feb 2001 05:09:13 -0500 (EST) (envelope-from dev@archonet.com) Received: from mainbox.archonet.com (mwynhau.demon.co.uk [193.237.186.96]) by www-uk-05.digiserve.com (8.9.1/8.9.1) with ESMTP id KAA29684; Sun, 11 Feb 2001 10:08:16 GMT Received: from archonet.com (ibook.archonet.com [192.168.1.32]) by mainbox.archonet.com (Postfix) with ESMTP id 62E0F29F1E; Sun, 11 Feb 2001 08:23:17 +0000 (GMT) Message-ID: <3A864C03.A7561E33@archonet.com> Date: Sun, 11 Feb 2001 08:23:31 +0000 From: Richard Huxton X-Mailer: Mozilla 4.7C-CCK-MCD {C-UDP; EBM-APPLE} (Macintosh; I; PPC) X-Accept-Language: en MIME-Version: 1.0 To: Albert REINER Cc: pgsql-sql@postgresql.org Subject: Re: fetching the id of a new row References: <20010209203020.A205@frithjof> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200102/196 X-Sequence-Number: 1383 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