Retrieving PK of inserted row

From: "woger151" <woger151(at)jqpx37(dot)cotse(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Retrieving PK of inserted row
Date: 2007-01-30 11:19:48
Message-ID: 001c01c74460$965572c0$6501a8c0@apollosjf
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I want to retrieve the primary key, which is a SERIAL, of a row I just
inserted.

In 8.2, I could use RETURNING, but I'm using 8.1.

Reading around, I've seen the following methods discussed:
(1) Within a transation, do the INSERT, and then do a SELECT CURVAL
(2) Not necessarily within a transaction, get a candidate for the pk using
SELECT NEXTVAL, then INSERT the row.
(3) Use LASTVAL

My questions:
* Are any of these methods flawed?
* Is there any reason to prefer (1) to (2)?
* I'm not sure whether LASTVAL is guaranteed to work outside of a
transaction. Its description in the 8.1 manual starts with "Return the
value most recently returned by nextval in the current session. ..." but I'm
not sure of the formal sense in which "session" is being used. (I'm
connecting to the DB from mod_php in apache, so I guess "session" could mean
"during the existence of a given db connection," but I'm not sure.)

TIA

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Willy-Bas Loos 2007-01-30 11:26:51 Re: Unauthorized users can see db schema and read functions
Previous Message Peter Eisentraut 2007-01-30 11:14:50 Re: PostgreSQL 9.0