Re: 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
Import Notes
Reply to msg id not found: JDEFKDKCIFCAABOIMHJGAEFMCCAA.rsanford@trefs.comReference msg id not found: JDEFKDKCIFCAABOIMHJGAEFMCCAA.rsanford@trefs.com
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
Show quoted text
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
Show quoted text
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