Thread: Sequences, defaults and getting a new key

When the primary key of a table defaults to the nextval() of a
sequence, if you insert a new record into the table and accept the
default, how do you then select the value that was just used as the
new primary key and guarantee that you don't accidentally get the
value that another user just generated at about the same instant?

Thanks,

Jeff Willden

Jeff Willden <jeff@pavanell.com> writes:
> When the primary key of a table defaults to the nextval() of a
> sequence, if you insert a new record into the table and accept the
> default, how do you then select the value that was just used as the
> new primary key and guarantee that you don't accidentally get the
> value that another user just generated at about the same instant?

currval() or INSERT RETURNING would do it.  Check the archives for
a few thousand prior discussions ...

            regards, tom lane

On 22/02/2008, at 5:28, Jeff Willden wrote:

> When the primary key of a table defaults to the nextval() of a
> sequence, if you insert a new record into the table and accept the
> default, how do you then select the value that was just used as the
> new primary key and guarantee that you don't accidentally get the
> value that another user just generated at about the same instant?

select lastval() => Return value most recently obtained with nextval
currval(regclass) => Return value most recently obtained with nextval
for specified sequence

they work in the current session, so you'll never get another id from
another user ...


http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html


regards,



raimon