Skip site navigation (1) Skip section navigation (2)

Re: How to get automatically generated "id" field

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Nikola Milutinovic <Nikola(dot)Milutinovic(at)ev(dot)co(dot)yu>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: How to get automatically generated "id" field
Date: 2005-03-12 23:48:27
Message-ID: 42337FCB.3040401@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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(at)postgresql(dot)org so that your
>      message can get through to the mailing list cleanly
>
>

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


In response to

pgsql-jdbc by date

Next:From: Manohar KaulDate: 2005-03-13 00:59:21
Subject: help with driver info
Previous:From: Nikola MilutinovicDate: 2005-03-12 22:24:53
Subject: How to get automatically generated "id" field

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group