Horton

Thread

  1. LAST_INSERT_ID equivalent

    Erik Price <eprice@ptc.com> — 2003-06-12T17:14:59Z

    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

  2. Re: LAST_INSERT_ID equivalent

    Edmund Dengler <edmundd@esentire.com> — 2003-06-12T17:35:00Z

    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
>

  3. Re: LAST_INSERT_ID equivalent

    Richard Huxton <dev@archonet.com> — 2003-06-12T17:42:28Z

    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

  4. Re: LAST_INSERT_ID equivalent

    Erik Price <eprice@ptc.com> — 2003-06-12T17:44:16Z

    
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

  5. Re: LAST_INSERT_ID equivalent

    Darko Prenosil <darko.prenosil@finteh.hr> — 2003-06-12T17:50:23Z

    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 !

  6. Re: LAST_INSERT_ID equivalent

    Bruno Wolff III <bruno@wolff.to> — 2003-06-12T18:23:15Z

    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.

  7. Re: LAST_INSERT_ID equivalent

    Ericson Smith <eric@did-it.com> — 2003-06-12T18:28:25Z

    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
>  
>

  8. Re: LAST_INSERT_ID equivalent

    Erik Price <eprice@ptc.com> — 2003-06-12T19:17:22Z

    
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

  9. Re: LAST_INSERT_ID equivalent

    Bruno Wolff III <bruno@wolff.to> — 2003-06-12T19:26:53Z

    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.

  10. Re: LAST_INSERT_ID equivalent

    Ericson Smith <eric@did-it.com> — 2003-06-12T19:26:55Z

    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

  11. Re: LAST_INSERT_ID equivalent

    Arjen van der Meijden <acm@tweakers.net> — 2003-06-12T19:38:56Z

    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
>

  12. Re: LAST_INSERT_ID equivalent

    Nigel J. Andrews <nandrews@investsystems.co.uk> — 2003-06-21T14:10:41Z

    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
> >