From: Richard Huxton <dev@archonet.com>
To: Norman Khine <norman@spot5.com>
To: pgsql-sql@postgresql.org
Subject: Re: Multi table insert and passing sequence ids
Date: Tue, 6 Aug 2002 18:38:23 +0100
Message-ID: <200208061838.23195.dev@archonet.com> (raw)
In-Reply-To: <20020806123829.C1040-100000@reaper.spot5.com>
References: <20020806123829.C1040-100000@reaper.spot5.com>
On Tuesday 06 Aug 2002 1:55 pm, Norman Khine wrote:
> Hello,
> I have a database that has 4 tables
> I also have an html put form which is an almagamation of all the fields
> from these tables and am trying to write the sql which will allow me to
> put this data into the database, in particular I am unsure as to howto
> pass the sequence id from business_name to the business_address_1 FOREIGN
> KEY, perhaps I'll need to do it using 4 different sql statements?
Norman - see the thread "problem fetching currval of sequence" for someone
doing just this.
You will need 4 statements, but I don't see the sequence definition for
business_name
> CREATE TABLE business_name(
> business_name_id int4 NOT NULL CONSTRAINT UC_business_name1 UNIQUE,
> business_name varchar(40),
> business_url varchar(35),
> CONSTRAINT PK_business_name1 PRIMARY KEY (business_name_id));
You'll want a "DEFAULT nextval('business_name_id_seq')" (or whatever you call
the sequence) on business_name_id . Then you can do:
BEGIN;
INSERT INTO busines_name (business_name,business_url) VALUES (...);
INSERT INTO county (business_name_id,...) VALUES
(currval('business_name_id_seq',...)
etc...
COMMIT;
Wrapping the statements in begin/commit ensure they all take place in one
transaction and so either all happen or none do. The sequence value is
guaranteed to be unique for the current process, so two people can insert
entries at the same time.
If you didn't want to do the DEFAULT bit with business_name.business_name_id
(and I'd recommend you do) then you'd use nextval('business_name_id_seq') to
get the new id to use. Check the docs on currval/nextval/create sequence for
details.
HTH
- Richard Huxton
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: dev@archonet.com, norman@spot5.com
Subject: Re: Multi table insert and passing sequence ids
In-Reply-To: <200208061838.23195.dev@archonet.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