pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew G. Hammond <drew@xyzzy.dhs.org>
To: Roland Roberts <roland@astrofoto.org>
To: pgsql-sql@postgresql.org
Subject: Re: INSERT question
Date: Thu, 15 Nov 2001 16:23:16 -0500
Message-ID: <E164Tyh-0003Da-00@xyzzy.lan.internal> (raw)
In-Reply-To: <m21yizhkej.fsf@tycho.rlent.pnet>
References: <1f5f2b44.0111130130.17e8e57a@posting.google.com>
	<878zd7vpzn.fsf@npa01zz001.simplot.com>
	<m21yizhkej.fsf@tycho.rlent.pnet>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 2001 November 15 03:03 pm, Roland Roberts wrote:
> >>>>> "Jason" == Jason Earl <jason.earl@simplot.com> writes:
>
>     Jason> Even better, however, is to use the functions nextval() and
>     Jason> currval() like so:
>
>     Jason> INSERT INTO parent_table (data) values ('some data');
>     Jason> INSERT INTO child_table (parent, more_data)
>     Jason>         (currval('parent_table_p_key_seq'),
>     Jason>          'more data');
>
> The above is probably the best for a pure-SQL way with the caveat
> that it should be wrapped in a transaction or currval() may not be
> what you expect; i.e., another client may add a row and you get a
> value different from what you inserted.

Actually, I don't think you need the transaction.  currval() "may not be what 
you expect" in a very different way from what you're impling above!

Session1: SELECT nextval('test')	# 1
Session2: SELECT nextval('test')	# 2
Session1: SELECT currval('test')	# 1     !!!!!!!
Session1: SELECT nextval('test')	# 3

Kinda counter-intuitive, but fits the definition.

- -- 
Andrew G. Hammond     mailto:drew@xyzzy.dhs.org   http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F                  613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iEYEARECAAYFAjv0MkUACgkQCT73CrRXhLEVkwCdGmGvMzc4y3emRPKxWGoGpPiL
WO8AnRekzrv9XVicymCtsplg0QbKFF1M
=iTsp
-----END PGP SIGNATURE-----



view thread (12+ messages)  latest in thread

Message-ID: <E164Tyh-0003Da-00@xyzzy.lan.internal>
Permalink:  ../E164Tyh-0003Da-00@xyzzy.lan.internal/
Also on:    postgresql.org/message-id/E164Tyh-0003Da-00@xyzzy.lan.internal

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: drew@xyzzy.dhs.org, roland@astrofoto.org
  Subject: Re: INSERT question
  In-Reply-To: <E164Tyh-0003Da-00@xyzzy.lan.internal>

* 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