Received: from mail.astrofoto.org (dsl092-102-177.nyc2.dsl.speakeasy.net [66.92.102.177]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAFGBPm19389 for ; Thu, 15 Nov 2001 11:11:25 -0500 (EST) (envelope-from roland@astrofoto.org) Received: (from roland@localhost) by mail.astrofoto.org (8.11.6/8.11.6) id fAFGBUC15111; Thu, 15 Nov 2001 11:11:30 -0500 To: pgsql-sql@postgresql.org Subject: Re: INSERT question References: <1f5f2b44.0111130130.17e8e57a@posting.google.com> From: Roland Roberts Date: 15 Nov 2001 11:11:30 -0500 In-Reply-To: <1f5f2b44.0111130130.17e8e57a@posting.google.com> Message-ID: Lines: 39 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.0.105 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200111/216 X-Sequence-Number: 5599 >>>>> "Brian" == Brian writes: Brian> To insert values into the child table corresponding to an Brian> entry in the parent table, how do I get a reference to the Brian> serial primary key (so as I can reference it for the Brian> foreign key entry) To do this in a transaction-safe manner you need to explicitly select the "parent" row and get it's primary key. That, of course, can be tricky. The way I've usually seen this done is to explicitly pull a value from the sequence and use it for both inserts, e.g., select nextval('sequence_name'); insert into foo values ($seqno, ...); insert into bar values ($seqno, ...); The above is sketchy because I don't know how you are accessing the database. If you are using Perl or Tcl or somesuch, you will need to save the value from the select and use in the inserts. Brian> Hope you understand what I mean. This should be a regular Brian> occurance and seeing as I'm not an sql guru, I just don't Brian> have a clue! One question here: when you say "parent" and "child" are you referring to derived tables or are you just trying to describe the foreign key constraint? PostgreSQL has "child" tables in the sense of create table foo ( a serial ); create table bar ( b varchar(32) ) inherits (foo); 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