how to return the last inserted identity column value

On Thu, Mar 8, 2012 at 11:16 AM, <mgould@isstrucksoftware.net> wrote:

In some languges you can use set l_localid = @@identity which returns
the value of the identity column defined in the table.  How can I do
this in Postgres 9.1

Assuming you created a table like so:

smarlowe=# create table test (id serial,info text);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for
serial column "test.id"
CREATE TABLE

Then use returning:

smarlowe=# insert into test (info) values ('this is a test') returning id;
id
----
1
(1 row)

Hi,

On 9 March 2012 05:20, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Thu, Mar 8, 2012 at 11:16 AM,  <mgould@isstrucksoftware.net> wrote:

In some languges you can use set l_localid = @@identity which returns
the value of the identity column defined in the table.  How can I do
this in Postgres 9.1

Assuming you created a table like so:

smarlowe=# create table test (id serial,info text);
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for
serial column "test.id"
CREATE TABLE

Then use returning:

smarlowe=# insert into test (info) values ('this is a test') returning id;

You can use lastval() or currval() functions:
http://www.postgresql.org/docs/9.1/static/functions-sequence.html

--
Ondrej Ivanic
(ondrej.ivanic@gmail.com)