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