RE: Re: OID as Primary Key - Mailing list pgsql-general

That's FAQ 4.16.2/4.16.3:
4.16.2) How do I get the value of a SERIAL insert?
One approach is to to retrieve the next SERIAL value from the sequence
object with the nextval() function before inserting and then insert it
explicitly. Using the example table in 4.16.1, that might look like this:
    $newSerialID = nextval('person_id_seq');
    INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
You would then also have the new value stored in $newSerialID for use in
other queries (e.g., as a foreign key to the person table). Note that the
name of the automatically created SEQUENCE object will be named
<table>_<serialcolumn>_seq, where table and serialcolumn are the names of
your table and your SERIAL column, respectively.
Alternatively, you could retrieve the assigned SERIAL value with the
currval() function after it was inserted by default, e.g.,
    INSERT INTO person (name) VALUES ('Blaise Pascal');
    $newID = currval('person_id_seq');
Finally, you could use the OID returned from the INSERT statement to look
up the default value, though this is probably the least portable approach.
In Perl, using DBI with Edmund Mergl's DBD::Pg module, the oid value is
made available via $sth->{pg_oid_status} after $sth->execute().
4.16.3) Don't currval() and nextval() lead to a race condition with other
users?
No. This is handled by the backends.

Mike Mascari
mascarm@mascari.com

-----Original Message-----
From:    Jonas Bengtsson [SMTP:jonas.b@home.se]
Sent:    Thursday, March 22, 2001 11:54 AM
To:    pgsql-general@postgresql.org
Subject:    RE: Re: [GENERAL] OID as Primary Key

Isn't is better to use nextval(). If there are many simultaneous users
vurrval might give the value of another insert.

/Jonas B

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Michael Ansley
Sent: Thursday, March 22, 2001 5:50 PM
To: pgsql-general@postgresql.org
Subject: RE: Re: [GENERAL] OID as Primary Key

And you can get the last SERIAL inserted using currval.
MikeA

>> -----Original Message-----
>> From: Brent R. Matzelle [ mailto:bmatzelle@yahoo.com
<mailto:bmatzelle@yahoo.com> ]
>> Sent: 22 March 2001 16:36
>> To: pgsql-general@postgresql.org
>> Subject: Re: Re: [GENERAL] OID as Primary Key
>>
>>
>> --- Jonas Bengtsson <jonas.b@home.se> wrote:
>> > Can't you do a dump with the oid's?
>>
>> Yes, but I would never suggest it.
>>
>> > But when I want to know the primary key of the inserted row
>> > I have to do an extra select query. If I use oid I just use
>> > pg_getlastoid() in php.
>> > And it is redundant data to store another integer.
>>
>> You should not use oid's as primary keys just because you want
>> to save a little HD space.
>>
>> I contributed a some code on PHPBuilder for a function that
>> returns the insert id of a SERIAL primary key.  You can view it
>> here:
>> http://www.phpbuilder.com/snippet/download.php?type=snippet&id=304
<http://www.phpbuilder.com/snippet/download.php?type=snippet&id=304>
>> .  It works well for me and it does not kludge my code like
>> using the oid does.
>>
>> Brent