Обсуждение: getting a sequence value
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.
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@postgresql.org
>
>
>
>
--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561
Dave Cramer <pg 'at' fastcrypt.com> writes:
> 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.
Which can be wrong in case of multithreaded applications without
synchronization over the two requests, if I'm correct.
--
Guillaume Cottenceau
Nico schrieb:
>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(???, ...);
>
>
>
How about INSERT INTO "tblTable2 ("Field2", ...) VALUES
(currval('public."TbleTable1_FieldID_seq"'), ...);
But you have to do this in the same connection to the database, that did
insert into table 1.
--
Roland Walter
MOSAIC SOFTWARE AG
Telefon: 02225/882-411 Fax: 02225/882-201
http://www.mosaic-ag.com
Guillaume,
No it can't be wrong from the database's point of view, now what happens
in java is another problem
Here's how it works, in principle anyway.
The last value of each sequence which has been generated using nextval
is stored in the connections session memory.
so when you use the SAME connection to retrieve currval it will retrieve
that value.
If you get a different connection, or store it in a shared value in your
java then it doesn't work.
Dave
Guillaume Cottenceau wrote:
>Dave Cramer <pg 'at' fastcrypt.com> writes:
>
>
>
>>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.
>>
>>
>
>Which can be wrong in case of multithreaded applications without
>synchronization over the two requests, if I'm correct.
>
>
>
--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561
Dave Cramer <pg 'at' fastcrypt.com> writes: > Guillaume, > > No it can't be wrong from the database's point of view, now what > happens in java is another problem Yes, of course :) > Here's how it works, in principle anyway. > > The last value of each sequence which has been generated using nextval > is stored in the connections session memory. > so when you use the SAME connection to retrieve currval it will > retrieve that value. We agree. And what the original poster wanted to do with his data will be incorrect. -- Guillaume Cottenceau
Roland,
That works too! Great idea.
I'm so used to doing it the other way as most times I need the primary
key too in my java class for identity purposes.
Dave
Roland Walter wrote:
> Nico schrieb:
>
>> 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(???, ...);
>>
>>
>>
> How about INSERT INTO "tblTable2 ("Field2", ...) VALUES
> (currval('public."TbleTable1_FieldID_seq"'), ...);
>
> But you have to do this in the same connection to the database, that
> did insert into table 1.
>
--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561
Sorry, misunderstood you. Dave Guillaume Cottenceau wrote: >Dave Cramer <pg 'at' fastcrypt.com> writes: > > > >>Guillaume, >> >>No it can't be wrong from the database's point of view, now what >>happens in java is another problem >> >> > >Yes, of course :) > > > >>Here's how it works, in principle anyway. >> >>The last value of each sequence which has been generated using nextval >>is stored in the connections session memory. >>so when you use the SAME connection to retrieve currval it will >>retrieve that value. >> >> > >We agree. And what the original poster wanted to do with his data >will be incorrect. > > > -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561
Thank you,
Nico.
"Roland Walter" <rwa@mosaic-ag.com> schreef in bericht
news:423EC07B.6080406@mosaic-ag.com...
> Nico schrieb:
>
>>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(???, ...);
>>
>>
> How about INSERT INTO "tblTable2 ("Field2", ...) VALUES
> (currval('public."TbleTable1_FieldID_seq"'), ...);
>
> But you have to do this in the same connection to the database, that did
> insert into table 1.
>
> --
> Roland Walter
> MOSAIC SOFTWARE AG
> Telefon: 02225/882-411 Fax: 02225/882-201
> http://www.mosaic-ag.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>