Обсуждение: 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
"Robert J. Sanford, Jr." <rsanford@trefs.com> writes:
> 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)
Use a serial column as the primary key, and then @@IDENTITY can be
implemented as currval() on the associated sequence. This is more
flexible than what you describe for SQL Server, because the currval
can be retrieved at any later time in the same session --- as long
as you don't do another insert into the same table, it's still good.
regards, tom lane
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. 2) Can I lock the sequence to make sure that another INSERT doesn't occur before I select the currval() of the sequence? rjsjr > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Saturday, May 18, 2002 5:52 PM > To: Robert J. Sanford, Jr. > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] how to get id of last insert on a > serial type? > > > "Robert J. Sanford, Jr." <rsanford@trefs.com> writes: > > 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) > > Use a serial column as the primary key, and then @@IDENTITY can be > implemented as currval() on the associated sequence. This is more > flexible than what you describe for SQL Server, because the currval > can be retrieved at any later time in the same session --- as long > as you don't do another insert into the same table, it's > still good. > > regards, tom lane >
> -----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
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. 2) Can I lock the sequence to make sure that another INSERT doesn't occur before I select the currval() of the sequence? rjsjr > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Saturday, May 18, 2002 5:52 PM > To: Robert J. Sanford, Jr. > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] how to get id of last insert on a > serial type? > > > "Robert J. Sanford, Jr." <rsanford@trefs.com> writes: > > 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) > > Use a serial column as the primary key, and then @@IDENTITY can be > implemented as currval() on the associated sequence. This is more > flexible than what you describe for SQL Server, because the currval > can be retrieved at any later time in the same session --- as long > as you don't do another insert into the same table, it's > still good. > > regards, tom lane >