Re: returning autogenerated keys

[Adriaan Joubert <a(dot)joubert(at)albourne(dot)com>]

Yes, that is correct, postgres does not support returning auto-generated 
keys.

You can get the auto generated keys by calling

SELECT currval('<sequence-name>');

on the same connection. We have remote users and latency is an issue. We 
combine the insert and the fetch of the remote keys, which does 
everything in a single trip to the database.

sql = "INSERT ....; SELECT currval('<sequence-name>')";
// Prepare the statement
PreparedStatement st = connection.prepareStatement(sql);
// Set the insert values
...
// Insert the row
st.execute();
int nInserted = st.getUpdateCount();
if (nInserted == 1 && st.getMoreResults()) {
   ResultSet rs = st.getResultSet();
   if (rs.next())
       result = rs.get(1);
}

That does everything in one go.

Cheers,

Adriaan



Srinivasa_Tata(at)hud(dot)gov wrote:

>  
> I am trying to use PostgreSQL and while trying to do a prototype, I am 
> getting an error.  
>
> org.postgresql.util.PSQLException: Returning autogenerated keys is not 
> supported.
> at 
> org.postgresql.jdbc3.AbstractJdbc3Statement.executeUpdate(AbstractJdbc3Statement.java:138)
>
> In the TODO list (INSERT section), it is mentioned that  "This is 
> useful for returning the auto-generated key for an INSERT. One 
> complication is how to handle rules that run as part of the insert."
>
> Does it mean PostgreSQL does not support returning autogenerated keys? 
>  I am using 803 version.
>
> Thanks In Advance
>
> Srinivas Tata_
>
> _

begin:vcard
fn:Adriaan Joubert
n:Joubert;Adriaan
org:Albourne Partners (Cyprus) Ltd
adr:;;3 D. Vikella Street;Nicosia;;1061;Cyprus
email;internet:a(dot)joubert(at)albourne(dot)com
tel;work:+357 22 750652
tel;fax:+357 22 750654
x-mozilla-html:FALSE
url:http://www.albourne.com
version:2.1
end:vcard