In table two you would not create a serial instead you would create an
INTEGER because serial is a counter and the values in table 2 may not be
in table 1.
Use a transaction like as follows
BEGIN;
INSERT INTO TABLE1 VALUES (Whatever values);
var = SELECT CURRVAL('sequence_name');
INSERT INTO TABLE2 VALUES (var,whatever else);
COMMIT;
So you would start the transaction then insert into the first table.
You would then get the current value of the sequence that the first table
created.
You would then insert this value into table2 along with anything else
HTH
Darren Ferguson
On Tue, 26 Mar 2002, power2themacs wrote:
> I am using 7.2 and JDBC. I have a very simple situation where I
> insert an item with a primary key which is a SERIAL. In the same
> transaction I need to insert a reference in a separate table to this
> item. Of course, race conditions could occur if I didn't use this
> special isolation level. But is there no way in which I could use
> row-level locking instead? Certainly, I am not updating the table I
> just inserted to, so the FOR UPDATE would never unlock. Is the
> isolation level my only option? I noticed that psql displays the oid
> after an INSERT. That would be exactly what I need but JDBC doesn't
> seem to offer this.
>
> (Right now I set the isolation level and just get the SERIAL's current value.)
>
> |---table1----------------| |---table2-----------------|
> | id SERIAL PRIMARY KEY | | id SERIAL REFERNCES table1 |
>
>
> Short version:
> I just inserted into table1 and need these in the same transaction.
> How can I get the id I just inserted into table2? Thanks folks.
> --
> ><><><><><><><><><><><><
> power2themacs@yahoo.com
>
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>