pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Roland Roberts <roland@astrofoto.org>
To: pgsql-sql@postgresql.org
Subject: Re: INSERT question
Date: 15 Nov 2001 11:11:30 -0500
Message-ID: <m2snbgggjx.fsf@tycho.rlent.pnet> (raw)
In-Reply-To: <1f5f2b44.0111130130.17e8e57a@posting.google.com>
References: <1f5f2b44.0111130130.17e8e57a@posting.google.com>

>>>>> "Brian" == Brian  <Brian@McSweeney.iol.ie> 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



view thread (12+ messages)  latest in thread

Message-ID: <m2snbgggjx.fsf@tycho.rlent.pnet>
Permalink:  ../m2snbgggjx.fsf@tycho.rlent.pnet/
Also on:    postgresql.org/message-id/m2snbgggjx.fsf@tycho.rlent.pnet

 · 

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: roland@astrofoto.org
  Subject: Re: INSERT question
  In-Reply-To: <m2snbgggjx.fsf@tycho.rlent.pnet>

* 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