Re: Insert data into multiple tables
От | will trillich |
---|---|
Тема | Re: Insert data into multiple tables |
Дата | |
Msg-id | 20010418004809.A20896@serensoft.com обсуждение исходный текст |
Ответ на | Insert data into multiple tables ("K Old" <kevsurf4@hotmail.com>) |
Список | pgsql-general |
On Tue, Apr 17, 2001 at 05:37:31PM -0500, K Old wrote: > Hello, > > I have a general question about inserting data. > > I have the following tables: > > CREATE TABLE customer ( > client_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, is this the same as client_id SERIAL ? > addr_id INT, > account_type INT, > how_hear INT, > cc_type INT, > cc_exp VARCHAR(5), > cc_num VARCHAR(16), > sign_up_date DATE > ); > > CREATE TABLE addresses ( > addr_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, > fname VARCHAR(16), > lname VARCHAR(16), > company VARCHAR(72), > addr1 VARCHAR(72), > addr2 VARCHAR(48), > city VARCHAR(32), > state VARCHAR(3), > zip VARCHAR(16), > country VARCHAR(32), > phone VARCHAR(18), > fax VARCHAR(18), > email VARCHAR(74) > ); > > I need to insert data into both of these tables at one time. I want to have > the "addr_id" field in the addresses tables to be included in the insert of > data to the customer table. according to your table definition, addr_id is just an int, which you apparently must supply to postgres if it's to be anything but NULL. seems like you'd be able to use that same value for the next insert. i'd wrap the two inserts within a begin work; insert into addresses .... insert into customers .... commit work; block, of course. > My solution so far is to have 1 insert statement that inserts the > appropriate data into the addresses table (creating a record), then have a > select statement pull back the most recent (which is only milliseconds old) > record from addresses and get the addr_id (it would probably be set to a > variable) then when all other data is gathered for the customer insert the > addr_id would be included in that insert. if you're trying to get the value of client_id instead, if it's a serial value, meaning it's got this neat trigger mechanism behind the scenes to bump the counter for every insert, then you can get your most-recently inserted serial value via select currval ( 'client_id_seq'::text ); (and if you do use the serial data type in defining a table, the "\d mytable" will show how it's used to populate new instances of that column: id | integer | not null default nextval('my_id_seq'::text) so "nextval" bumps the counter for you, andonce you do that, you can get the most recent value used via "currval()" -- don't visit this page. it's bad for you. take my expert word for it. http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html will@serensoft.com http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us!
В списке pgsql-general по дате отправления: