Re: getting a sequence value

you can either retrieve the nextval before inserting and insert it or
let the default constraint
insert it for you and retrieve it using currval

1)
select nextval('public."tblTable1_FieldID_seq"'::text)
then insert this value into table 1 and you will know the value beforehand

2)
allow the insert to go through
select currval('public."tblTable1_FieldID_seq"'::text)
and then use this to insert into table 2

FYI currval does the "right" thing. It is guaranteed to be the value
your connection retrieved from the sequence.

Dave

Nico wrote:

>Hi,
>I have two tables where the second table has a foreign key to the first one.
>When I insert a row in the first one, I need the value of the ID generated
>by a sequence in the first table to insert it in the foreign key column in
>the second table. Except I don't know how to do this in Java/PostgreSQL...
>Anyone knows how?
>Table data:
>Table 1:
>Field1 (integer) default: nextval('public."tblTable1_FieldID_seq"'::text)
>other field...
>Table 2:
>Field1 (integer) default: nextval('public."tblTable2_FieldID_seq"':text)
>Field2 (integer) foreign key that references to Field1 in Table 1...
>
>So when I do this:
>INSERT INTO "tblTable1"(other fields...) VALUES(...);
>how do I know what to put in here?
>INSERT INTO "tblTable2"("Field2", other fields...) VALUES(???, ...);
>
>I know in MS SQL server and Oracle one uses an old and new resultset to
>resolve this issue, but haven't a clue how to do this in Java/PostgreSQL...
>
>Nico.
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561