Received: from comptechnews.com (root@cc993546-b.srst1.fl.home.com [24.3.77.52]) by hub.org (8.10.1/8.10.1) with ESMTP id e5SKHF188615 for ; Wed, 28 Jun 2000 16:17:15 -0400 (EDT) Received: from comptechnews (reaster@localhost [127.0.0.1]) by comptechnews.com (8.9.3/8.9.3) with SMTP id QAA03524; Wed, 28 Jun 2000 16:19:01 -0400 From: "Robert B. Easter" To: "Lea, Michael" , "'pgsql-sql@postgresql.org'" Subject: Re: retrieving a serial number Date: Wed, 28 Jun 2000 16:12:38 -0400 X-Mailer: KMail [version 1.0.28] Content-Type: text/plain References: In-Reply-To: MIME-Version: 1.0 Message-Id: <00062816190002.00209@comptechnews> Content-Transfer-Encoding: 8bit X-Archive-Number: 200006/268 You can do something like this: $var = SELECT nextval('seq_name'); INSERT INTO master (id, ...) VALUES ($var, ...); INSERT INTO slave1 (id, ...) VALUES ($var, ...); ... You can do a \d master, in psql to find out the name of the sequence. For the SERIAL type, nextval('seq_name') is just its default anyway. On Wed, 28 Jun 2000, Lea, Michael wrote: > I have one table (call it "master") that, among other things, contains a > serial number. I have a number of other tables (call them "slaves") that, > among other things, contain a foreign key referring to the serial number in > the first table. I will be inserting one row into the master table, and one > row into zero or more slave tables in each transaction. > > What I want to do is use a sequence in the master table to let PostgreSQL > automatically generate a unique serial number for me, but I need to know the > serial number in order to insert the corresponding rows into the slave > table(s). Is there any way of retrieving the serial number that will be used > before the transaction has been committed? Or will I have to generate my own > serial numbers? > > Michael Lea > Information Security > Manitoba Public Insurance > Phone: (204) 985-8224 -- Robert B. Easter