Обсуждение: How to get automatically generated "id" field

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

...start transaction
executeQuery( "SELECT max( id )+1 AS new_id FROM photo" );
..read "new_id"
executeUpdate( "INSERT INTO photo (id, size, file) VALUES (...)" );
..commit

Nix.

Nikola,

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.

Dave

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
> transaction:
>
> ...start transaction
> executeQuery( "SELECT max( id )+1 AS new_id FROM photo" );
> ..read "new_id"
> executeUpdate( "INSERT INTO photo (id, size, file) VALUES (...)" );
> ..commit
>
> Nix.
>
> ---------------------------(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
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561