pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jason Earl <jason.earl@simplot.com>
To: Roland Roberts <roland@astrofoto.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: INSERT question
Date: 15 Nov 2001 14:28:26 -0700
Message-ID: <87y9l7u3k5.fsf@npa01zz001.simplot.com> (raw)
In-Reply-To: <m21yizhkej.fsf@tycho.rlent.pnet>
References: <1f5f2b44.0111130130.17e8e57a@posting.google.com>
	<3bf19679$0$189$e4fe514c@newszilla.xs4all.nl>
	<878zd7vpzn.fsf@npa01zz001.simplot.com>
	<m21yizhkej.fsf@tycho.rlent.pnet>


Yes that is entirely correct.  As you pointed out my example is *very*
dangerous if not wrapped in a transaction.  My query clearly should
have looked like this:

BEGIN;
INSERT INTO parent_table (data) values ('some data');
INSERT INTO child_table (parent, more_data) 
        (currval('parent_table_p_key_seq'), 
         'more data');
COMMIT;

Thanks for pointing that out.

Roland Roberts <roland@astrofoto.org> writes:

> >>>>> "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.
> 
> roland
> -- 
> 		       PGP Key ID: 66 BC 3B CD
> Roland B. Roberts, PhD                             RL Enterprises
> roland@rlenter.com                     76-15 113th Street, Apt 3B
> roland@astrofoto.org                       Forest Hills, NY 11375
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)



view thread (12+ messages)  latest in thread

Message-ID: <87y9l7u3k5.fsf@npa01zz001.simplot.com>
Permalink:  ../87y9l7u3k5.fsf@npa01zz001.simplot.com/
Also on:    postgresql.org/message-id/87y9l7u3k5.fsf@npa01zz001.simplot.com

 · 

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: jason.earl@simplot.com, roland@astrofoto.org
  Subject: Re: INSERT question
  In-Reply-To: <87y9l7u3k5.fsf@npa01zz001.simplot.com>

* 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