While the below works it will be very slow after you have many id's. The
correct way to do this is to use either nextval('sequence_name') before
the insert and then
insert this value, or use currval('sequence_name') after the insert. You
don't have to do this in a transaction. If you use currval you need to
make sure it is the same connection.
yes, this is postgres specific, but it is the way to do it.
Nikola Milutinovic wrote:
> Hi all.
> This may be a bit more for general ML, but I'm in a fix and need an
> advice. I have several tables with auto-generated "id" field, like this.
> CREATE TABLE photo (
> id SERIAL PRIMARY KEY,
> size INT8,
> file VARCHAR(256)
> I would like to be able to insert values and get back the "id" of a
> newly inserted record. What is a good way to do it?
> I imagine I could open a transaction and read the value of the
> sequence, but that is ugly, since the sequence name is autogenerated
> and PG specific.
> Would it be better to change "SERIAL" to just INT8 and do in a
> ...start transaction
> executeQuery( "SELECT max( id )+1 AS new_id FROM photo" );
> ..read "new_id"
> executeUpdate( "INSERT INTO photo (id, size, file) VALUES (...)" );
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
519 939 0336
In response to
pgsql-jdbc by date
|Next:||From: Manohar Kaul||Date: 2005-03-13 00:59:21|
|Subject: help with driver info|
|Previous:||From: Nikola Milutinovic||Date: 2005-03-12 22:24:53|
|Subject: How to get automatically generated "id" field|