Thread: getting a sequence value

getting a sequence value

From

"Nico"

Date:

21 March 2005, 14:28:41

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.

Re: getting a sequence value

From

Dave Cramer

Date:

21 March 2005, 15:29:02

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

Re: getting a sequence value

From

Guillaume Cottenceau

Date:

21 March 2005, 15:37:50

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

Re: getting a sequence value

From

Roland Walter

Date:

21 March 2005, 15:43:17

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

Re: getting a sequence value

From

Dave Cramer

Date:

21 March 2005, 15:45:13

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

Re: getting a sequence value

From

Guillaume Cottenceau

Date:

21 March 2005, 15:52:50

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

Re: getting a sequence value

From

Dave Cramer

Date:

21 March 2005, 15:53:53

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

Re: getting a sequence value

From

Dave Cramer

Date:

21 March 2005, 15:55:27

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

Re: getting a sequence value

From

"Nico"

Date:

21 March 2005, 20:00:38

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
>