Re: Referencing serial col's sequence for insert

On Mon, 2014-07-21 at 10:00 +0800, Anil Menon wrote:
> Hi,
>
>
> 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?
>
>
> Thanks in advance,
>
> AK
>
>
>
>

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.

HTH.

Robert