Thread: how to return the last inserted identity column value

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

Michael Gould
Intermodal Software Solutions, LLC
904-226-0978

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)