Re: Referencing serial col's sequence for insert

rob stone wrote:
>> I have a question on the right/correct practice on using the serial
>> col's sequence for insert.
>>
>> Best way of explanation is by an example:
>>
>> create table id01 (col1 serial, col2 varchar(10));
>>
>> insert into id01(col2) values ( 'data'||
>> currval('id01_col1_seq')::varchar);
>>
>> while I do get what I want:
>>
>> select * from id01;
>> col1 | col2
>> ------+-------
>> 1 | data1
>>
>> Is this guaranteed to work : I am assuming that an insert triggers the
>> id01_col1_seq's nextval first hence using
>> id01_col1_seq's currval subsequently will have the "correct" /
>> expected value (and not the previous value before the insert).
>>
>> Is my assumption correct?

> I would do the following:-
>
> create table id01 (col1 serial NOT NULL PRIMARY KEY, col2 varchar(10));
>
> In a try . . catch block:-
>
> BEGIN;
> INSERT INTO id01 (col2) VALUES ('data');
> SELECT lastval() AS last_row_id;
> COMMIT; or ROLLBACK; if you have errors.
>
> There is also "insert . . returning" syntax which can make the value
> assigned to the serial column available to your application. I prefer
> using the "select lastval()" method.

Your example seems incomplete.

Also, I think that your method is vulnerable to race conditions:
If somebody else increments the sequence between the INSERT and
"SELECT lastval()" you'd get a wrong value.

The same might hold for the original example.

I would suggest something like that:

WITH seq(i) AS (SELECT nextval('id01_col1_seq'))
INSERT INTO id01 (col1, col2) (SELECT i, 'data' || i FROM seq);

Yours,
Laurenz Albe