Re: Fw: postgresql experts please help

> Any chance you have two man_id_seq sequences in different schemas? The
> table insert might use one and the direct currval/nextval calls might use
> another?

I don't know how that happened but I think that's right:
select of the row after insert returned ID=2,
but somehow subsequent call to sequence's nextval returned 216.

In the docs I discovered "select lastval()" which seems to behave exactly
like @@Identity in Sybase returning the latest nextval that was obtained in
the current session by no-matter-which sequence.
That's exactly the functionality I need - much easier to use because no need
to track the name.

Can you think of any reason why using "select lastval()" would be a bad
idea?

Thanks a lot,

Andrei

----- Original Message -----
From: "Kris Jurka" <books(at)ejurka(dot)com>
To: "Andrei Ilitchev" <andrei(dot)ilitchev(at)oracle(dot)com>
Cc: <pgsql-jdbc(at)postgresql(dot)org>; "Marina Vatkina" <Marina(dot)Vatkina(at)Sun(dot)COM>
Sent: Thursday, October 18, 2007 1:45 PM
Subject: Re: [JDBC] Fw: postgresql experts please help

>
>
> On Thu, 18 Oct 2007, Andrei Ilitchev wrote:
>
>> I created a table with identity:
>> CREATE TABLE MAN (ID SERIAL, NAME VARCHAR(40), PRIMARY KEY (ID))
>> then through jdbc connection inserted a row into the table,
>> then attempted to select pk value back through the same jdbc connection
>> using currval - that failed with "not yet defined in this session"
>> exception.
>>
>
> Any chance you have two man_id_seq sequences in different schemas? The
> table insert might use one and the direct currval/nextval calls might use
> another?
>
> Kris Jurka
>