how to get id of last insert on a serial type?
I am in the process of converting a java application from MS SQL
Server over to Postgres. The application heavily abstracts
interaction with the database and unfortunately appears to be making
some assumptions about functionality available within SQL Server
that I can't find information on duplicating inside of Postgres. The
one bit that I'm currently having the largest issue with is the
@@IDENTITY property. For those not familiar with SQL Server,
performing "SELECT @@IDENTITY" allows a user to retrieve the primary
key of the last row inserted into a table (assuming the table is
using an identity column as the primary key). In order for this to
work the SELECT must occur immediately after the insert on the same
connection. Any other SQL statements executed on that connection
will result in resetting of @@IDENTITY either to the next identity
value if an insert occurs or NULL otherwise. This is somewhat
restrictive but under the right conditions works exceedingly well.
I've done some searching through the mail archives but the only
instances of @@IDENTITY that I can find are two top-level questions
that don't appear to have any answers :( Searching through the
archives to find information on SERIAL types is a bit of a chore
with searches regularly returning > 1000 hits with the first several
hundred being non-topical. A pointer to the top level of a relevant
thread would be most welcome.
Reading through the documentation I am under the impression that I
could write a PL/PGSQL function that would select the next value of
an implicitly created sequence based on the serial datatype and then
perform an insert with that selected value and then return the value
from the function. But...
I sincerely hope that is not the only method of doing this becuase
that would mean that I have a LOT of code to rewrite.
Does anyone have any joy to share with me?
rjsjr
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Robert J.
Sanford, Jr.
Sent: Sunday, May 19, 2002 11:33 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] how to get id of last insert on a serial type?Two quick notes/questions...
1) That would still require me to write a function for each of
inserts to make sure that the value came back. I can't just
do a "SELECT @@IDENTITY" and get back the last identity that
was inserted, I have to know what sequence was used. The
current code base that is returning the @@IDENTITY doesn't
know anything about what was actually inserted, just that
something was.
Am I understanding correctly -- You're getting the PK but you don't
know/care which table it's from?
If so, then, no, in PG, you need to know the sequence name to use currval()
(or nextval(), below).
2) Can I lock the sequence to make sure that another INSERT
doesn't occur before I select the currval() of the sequence?
As long as you're the only user on this connection, you don't have to lock
anything to achieve this -- currval() will give you the last serial #
specific for this backend, even if there have been other inserts.
-- J.
Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant
Import Notes
Reply to msg id not found: JDEFKDKCIFCAABOIMHJGGEFOCCAA.rsanford@trefs.com | Resolved by subject fallback