Received: from charon.simplot.com (jrsmailhub.simplot.com [12.18.144.212]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAFLSVm46247 for ; Thu, 15 Nov 2001 16:28:32 -0500 (EST) (envelope-from earlj@xanadu.simplot.com) Received: from xanadu.simplot.COM (151.113.0.132) by charon.simplot.com (MX V5.1-A AnGp) with SMTP; Thu, 15 Nov 2001 14:28:30 -0700 Received: from npa01zz001.simplot.com by xanadu.simplot.COM; (5.65v4.0/1.1.8.2/20Aug97-1116AM) id AA28203; Thu, 15 Nov 2001 14:28:29 -0700 Received: from earlj by npa01zz001.simplot.com with local (Exim 3.32 #1 (Debian)) id 164U3f-0002ds-00; Thu, 15 Nov 2001 14:28:27 -0700 To: Roland Roberts CC: pgsql-sql@postgresql.org Subject: Re: INSERT question References: <1f5f2b44.0111130130.17e8e57a@posting.google.com> <3bf19679$0$189$e4fe514c@newszilla.xs4all.nl> <878zd7vpzn.fsf@npa01zz001.simplot.com> From: Jason Earl Date: 15 Nov 2001 14:28:26 -0700 In-Reply-To: Message-ID: <87y9l7u3k5.fsf@npa01zz001.simplot.com> Lines: 41 User-Agent: Gnus/5.0808 (Gnus v5.8.8) Emacs/21.1 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200111/228 X-Sequence-Number: 5610 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 writes: > >>>>> "Jason" == Jason Earl 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)