Postgres Pro
Downloads
Home   >   mailing lists

Thread: Get the last inserted id

Get the last inserted id

From
"Nacef LABIDI"
Date:
Hi all,
 
I was wondering if postgres can return the last ID inserted for a table which the primary key is autoincremented. I need to retrieve this ID in my code to continue processing on that inserted row.
 
Thanks to all

Re: Get the last inserted id

From
"Scott Marlowe"
Date:
On Fri, Apr 11, 2008 at 11:23 AM, Nacef LABIDI <nacef.l@gmail.com> wrote:
> Hi all,
>
> I was wondering if postgres can return the last ID inserted for a table
> which the primary key is autoincremented. I need to retrieve this ID in my
> code to continue processing on that inserted row.

smarlowe=# create table test (a serial primary key, b text);
NOTICE:  CREATE TABLE will create implicit sequence "test_a_seq" for
serial column "test.a"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE
smarlowe=# insert into test (a,b) values (DEFAULT,'test') returning a;a
---1
(1 row)

INSERT 0 1

Note that there are other ways of doing it, but I really like the
returning clause.

Re: Get the last inserted id

From
"Nacef LABIDI"
Date:
Nice idea but how can I use this in a frontend that I am developing using Delphi and ADO objects ?

On Fri, Apr 11, 2008 at 7:41 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Fri, Apr 11, 2008 at 11:23 AM, Nacef LABIDI <nacef.l@gmail.com> wrote:
> Hi all,
>
> I was wondering if postgres can return the last ID inserted for a table
> which the primary key is autoincremented. I need to retrieve this ID in my
> code to continue processing on that inserted row.

smarlowe=# create table test (a serial primary key, b text);
NOTICE:  CREATE TABLE will create implicit sequence "test_a_seq" for
serial column "test.a"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE
smarlowe=# insert into test (a,b) values (DEFAULT,'test') returning a;
 a
---
 1
(1 row)

INSERT 0 1

Note that there are other ways of doing it, but I really like the
returning clause.

Re: Get the last inserted id

From
"A. Kretschmer"
Date:
am  Fri, dem 11.04.2008, um 19:23:04 +0200 mailte Nacef LABIDI folgendes:
> Hi all,
>  
> I was wondering if postgres can return the last ID inserted for a table which
> the primary key is autoincremented. I need to retrieve this ID in my code to
> continue processing on that inserted row.

Either with RETURNING:
http://www.postgresql.org/docs/current/static/sql-insert.html

or with currval():
http://www.postgresql.org/docs/current/static/functions-sequence.html


HTH, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: Get the last inserted id

From
Andrew Sullivan
Date:
On Fri, Apr 11, 2008 at 07:23:04PM +0200, Nacef LABIDI wrote:
> Hi all,
> 
> I was wondering if postgres can return the last ID inserted for a table
> which the primary key is autoincremented. I need to retrieve this ID in my
> code to continue processing on that inserted row.

Use select currval() on the sequence name.

A