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

Re: getting primary key values for inserted records?

From: Jeremy Buchmann <jeremy(at)wellsgaming(dot)com>
To: "Andrew Nelson" <Andrew(dot)Nelson(at)hsc(dot)utah(dot)edu>
Cc: pgsql-jdbc <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: getting primary key values for inserted records?
Date: 2004-01-27 21:26:49
Message-ID: 844C4592-510F-11D8-BFB3-000A95D7EFFE@wellsgaming.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
On Jan 27, 2004, at 11:35 AM, Andrew Nelson wrote:

> I've always wondered if something like this is totally safe.  What if 
> someone somewhere else outside of my code ran a insert or update that 
> didn't use the sequence at all but changed the id.  And that id turned 
> out to be the same as the id collect from the select 
> nextval('sequence');
>
> So I guess you would need to add a lock of some sorts.
> Just wondering because this is something I have fought with for a long 
> time.

Well, it's only safe if you use the sequence.  If you don't, you're on 
your own.

Cheers,
Jeremy


>
>>>> Jeremy Buchmann <jeremy(at)wellsgaming(dot)com> 01/27/04 10:16AM >>>
>> I'am currently at the point, that after i insert a new Record in a
>> ResultSet
>> using insertRow() and using a serial for the primary key of the target
>> table.
>>
>> Of course i do not call rs.updateInt(primaryKey), as the sequence does
>> get me
>> the new value automatically.
>>
>> However - even using rs.refreshRow() doesn't get me the values, just a
>> requery
>> of the ResultSet.
>>
>> What is the way to go here?
>
> If you don't mind running two statements, you can select the next value
> from the sequence and then put it in your insert statement.  Like this:
>
> $pkey = "SELECT nextval('sequence_name')";
> "INSERT INTO table (id, ...) VALUES ($pkey, ...)";
>
> It's psuedo-code, but you get the idea.  This ensures that you know the
> primary key of the row you inserted, and the number came from the
> sequence so it's safe.
>
> Hope that helps,
> Jeremy
>
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
>


Responses

pgsql-jdbc by date

Next:From: Paul ThomasDate: 2004-01-27 22:01:30
Subject: Re: a little disillusioned
Previous:From: Thomas HallgrenDate: 2004-01-27 20:42:53
Subject: Pl/Java 1.0.0.b now avaiable on Linux 386 and Cygwin

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