Skip site navigation (1) Skip section navigation (2)

Re: How to obtain serial generate when doing an insert?

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: "Williams, Travis L, NPONS" <tlw(at)att(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: How to obtain serial generate when doing an insert?
Date: 2003-05-27 20:52:31
Message-ID: Pine.LNX.4.33.0305271447110.21159-100000@css120.ihs.com (view raw or flat)
Thread:
Lists: pgsql-general
On Tue, 27 May 2003, Williams, Travis L, NPONS wrote:

> I have a table with a serial field, how can I obtain what serial was 
> generated for the insert I'm doing?  I could just do another query for 
> max(id) .. where id is my serial field.. but I'm worried another insert 
> could have already came in.

There are a few ways to do this.  The "standard" way is pretty much this:

begin;
insert into table (field1,field2) values ('xxx','yyy');
select currval('seqname');
<more queries go here...>
commit;

OR

begin;
select nextval('seqname');
insert into table (field1,field2,id) values ('xxx','yyy',$valfromabove);
<more queries go here...>
commit;

You can also use OIDs, in some interfaces, the OID of the last inserted 
row is availble, like libpq which is used by PHP and C.  no need for a 
transaction:

$res = pg_query($conn,"insert into table ...");
$id = pg_last_oid($res);
$res2 - pg_query($conn,"select id from table where oid=$id");
print "Last inserted row oid is ";
print pg_result($res2,0,'id');

Since OIDs are optional, and may some day be deprecated for rows, it's not 
the best way...


In response to

pgsql-general by date

Next:From: Andrew SullivanDate: 2003-05-27 21:03:50
Subject: Re: Postgresql on SUN Server
Previous:From: Williams, Travis L, NPONSDate: 2003-05-27 20:44:43
Subject: How to obtain serial generate when doing an insert?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group