Postgres Pro
Downloads
Home   >   mailing lists

Thread: LAST_INSERT_ID equivalent

LAST_INSERT_ID equivalent

From
Erik Price
Date:
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.



Thanks,


Erik

Re: LAST_INSERT_ID equivalent

From
Edmund Dengler
Date:
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:

> 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.
>
>
>
> Thanks,
>
>
> Erik
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Re: LAST_INSERT_ID equivalent

From
Darko Prenosil
Date:
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 !

Re: LAST_INSERT_ID equivalent

From
Richard Huxton
Date:
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

Re: LAST_INSERT_ID equivalent

From
Erik Price
Date:

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

Re: LAST_INSERT_ID equivalent

From
Bruno Wolff III
Date:
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.

Re: LAST_INSERT_ID equivalent

From
Ericson Smith
Date:
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:

>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.
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>
>

Re: LAST_INSERT_ID equivalent

From
Erik Price
Date:

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

Re: LAST_INSERT_ID equivalent

From
Bruno Wolff III
Date:
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.

Re: LAST_INSERT_ID equivalent

From
Ericson Smith
Date:
No, it would only get the *next* value. Only one increment is performed.

Regards
- Ericson

Erik Price 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: LAST_INSERT_ID equivalent

From
Arjen van der Meijden
Date:
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

> -----Oorspronkelijk bericht-----
> Van: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] Namens Erik Price
> Verzonden: donderdag 12 juni 2003 19:15
> Aan: pgsql-general@postgresql.org
> Onderwerp: [GENERAL] LAST_INSERT_ID equivalent
>
>
> 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.
>
>
>
> Thanks,
>
>
> Erik
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>

Re: LAST_INSERT_ID equivalent

From
"Nigel J. Andrews"
Date:
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...]

> Arjen
>
> > -----Oorspronkelijk bericht-----
> > Van: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org] Namens Erik Price
> > Verzonden: donderdag 12 juni 2003 19:15
> > Aan: pgsql-general@postgresql.org
> > Onderwerp: [GENERAL] LAST_INSERT_ID equivalent
> >
> >
> > 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.
> >
> >
> >
> > Thanks,
> >
> >
> > Erik
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> > majordomo@postgresql.org
> >