LAST_INSERT_ID equivalent

Greetings all!

I believe
select currval('sequence_name');
should satisfy your needs. Within a transaction it will stay the same.

Regards!
Ed

On Thu, 12 Jun 2003, Erik Price wrote:

On Thursday 12 Jun 2003 6:14 pm, Erik Price wrote:

I have a table with a SEQUENCE on it that increments the primary key (a
BIGINT column) of the table whenever a new insert is performed.

Is there a way to determine the last incremented value, so that if I do
an insert, I can record the primary key of the record somewhere? I'm
interested in any technique for doing this, but especially a
JDBC-specific solution.

Sorry if the answer should be obvious but I am coming from MySQL and
trying to learn the ANSI equivalent of the MySQL features.

SELECT currval('sequence-name') - this handles multiple clients too. Not JDBC
specific.

Also look at nextval() and setval().

--
Richard Huxton

Edmund Dengler wrote:

Greetings all!

I believe
select currval('sequence_name');
should satisfy your needs. Within a transaction it will stay the same.

Ed, thanks, this looks like what I was looking for --

however, I am concerned by your disclaimer. Can you explain that a
little bit? I read it to mean "if you try to use this technique within
a transaction where you are INSERTing a new record, it will not reflect
the new record's ID". So then in order to determine the new record's ID
I would need to use

SELECT CURRVAL('sequence_name') + 1;

within the transaction.

Thanks,

Erik

On Thursday 12 June 2003 19:14, Erik Price wrote:

I have a table with a SEQUENCE on it that increments the primary key (a
BIGINT column) of the table whenever a new insert is performed.

Is there a way to determine the last incremented value, so that if I do
an insert, I can record the primary key of the record somewhere? I'm
interested in any technique for doing this, but especially a
JDBC-specific solution.

Sorry if the answer should be obvious but I am coming from MySQL and
trying to learn the ANSI equivalent of the MySQL features.

SELECT * FROM sequence_name;

and You got all data about sequence. There are also nextval, curval and setval
functions operating on the sequences.

Regards !

On Thu, Jun 12, 2003 at 13:44:16 -0400,
Erik Price <eprice@ptc.com> wrote:

Edmund Dengler wrote:

Greetings all!

I believe
select currval('sequence_name');
should satisfy your needs. Within a transaction it will stay the same.

Ed, thanks, this looks like what I was looking for --

however, I am concerned by your disclaimer. Can you explain that a
little bit? I read it to mean "if you try to use this technique within
a transaction where you are INSERTing a new record, it will not reflect
the new record's ID". So then in order to determine the new record's ID
I would need to use

SELECT CURRVAL('sequence_name') + 1;

within the transaction.

No. You just want to use currval. The comment was referring to other
transactions calling nextval while the transaction of interest is
proceeding.

While many others use currval(), we tend to grab the next ID provided by
nextval('seq') and use that to be inserted with the record. The process
is very atomic, and the ID is available to be used by the rest of your
program. The only drawback is if your insert query fails there will be a
hole in the sequence.

- Ericson Smith

Bruno Wolff III wrote:

Ericson Smith wrote:

While many others use currval(), we tend to grab the next ID provided by
nextval('seq') and use that to be inserted with the record. The process
is very atomic, and the ID is available to be used by the rest of your
program. The only drawback is if your insert query fails there will be a
hole in the sequence.

So you're saying that you perform a pre-query to fetch the nextval, then
you include that in your query where you perform the INSERT? I see.
Since this is all part of the same transaction, the nextval value won't
overwrite another simultaneous INSERT, I assume. This seems like a good
way to do it too. I don't mind the holes in the sequence, but wouldn't
this INSERT cause the sequence to increment the primary key yet again?

Erik

On Thu, Jun 12, 2003 at 15:17:22 -0400,
Erik Price <eprice@ptc.com> wrote:

So you're saying that you perform a pre-query to fetch the nextval, then
you include that in your query where you perform the INSERT? I see.
Since this is all part of the same transaction, the nextval value won't
overwrite another simultaneous INSERT, I assume. This seems like a good
way to do it too. I don't mind the holes in the sequence, but wouldn't
this INSERT cause the sequence to increment the primary key yet again?

If you do things that way you specify a value for the serial column
rather than let it default to using nextval.

No, it would only get the *next* value. Only one increment is performed.

Regards
- Ericson

Erik Price wrote:

When you can't use a transaction or don't want to use curval, you can
use this:

rowsUpdated = st.executeUpdate(); // Here's your insert
if(!update) // Update was just a boolean I used to differentiate between
updates and inserts, it's from a generic function
{
int lastOid =
((org.postgresql.jdbc1.AbstractJdbc1Statement)st).getInsertedOID();
String oidQuery = "SELECT " + idcolumn + " FROM " + table + " WHERE
oid = " + lastOid;
Statement oidSt = db.createStatement();
ResultSet oidRs = oidSt.executeQuery(oidQuery);
if(oidRs.next())
{
generatedKey = oidRs.getInt(1);
}
}

It's what I used to be a bit more certain about the curval and allowing
to forget about transactions if necessary :)
There is in JDBC3 a function specified to retrieve the last generated
key on a connection, but afaik it is still not implemented in
postgresql's JDBC-driver.

Arjen

It's going back a bit but I don't see any post replying to this in the hundreds
I have left unread in that time, so...

On Thu, 12 Jun 2003, Arjen van der Meijden wrote:

When you can't use a transaction or don't want to use curval, you can
use this:

rowsUpdated = st.executeUpdate(); // Here's your insert
if(!update) // Update was just a boolean I used to differentiate between
updates and inserts, it's from a generic function
{
int lastOid =
((org.postgresql.jdbc1.AbstractJdbc1Statement)st).getInsertedOID();
String oidQuery = "SELECT " + idcolumn + " FROM " + table + " WHERE
oid = " + lastOid;
Statement oidSt = db.createStatement();
ResultSet oidRs = oidSt.executeQuery(oidQuery);
if(oidRs.next())
{
generatedKey = oidRs.getInt(1);
}
}

It's what I used to be a bit more certain about the curval and allowing
to forget about transactions if necessary :)

1) While the oid method may well work it will _only_ work on tables that have
an oid field.

2) currval() has nothing to do with transactions, you can do a
nextval() followed by however many begin, commit or rollback statements you
desire and currval() will give you the same thing.

There is in JDBC3 a function specified to retrieve the last generated
key on a connection, but afaik it is still not implemented in
postgresql's JDBC-driver.

If you're looking to actually have a suitable method in your jdbc objects why
not simply code up your requirements in derived class and use that instead? I'd
have thought that was a near perfect example of object orientation.

--
Nigel J. Andrews

[rest of message follows...]