Re: Get last generated serial sequence and set it up when explicit value is used

Better use >= in pkey >= (select last_value ... ) :

  insert into mytab1 (name) values ('aaa')
       returning pkey, (select case when pkey >= (select last_value from mytab1_pkey_seq)
                                    then setval('mytab1_pkey_seq',pkey,true)
                                    else 0
                               end );

Seb

On 11/21/20 10:26 AM, Sebastien FLAESCH wrote:
> Hello everyone!
> 
> I believe I have a solution using a single SQL command.
> 
> Check this out... do you see any potential issues?
> 
> Any simpler way or more efficient code?
> 
> Note the insert with value 50, lower than previously inserted values, otherwise,
> the returning clause would just need to be
> 
>          returning pkey, setval('mytab1_pkey_seq',pkey,true)
> 
> Note also that I want to return the pkey to use the generated serial in the
> program code...
> 
> 
> =====
> 
> create table mytab1 ( pkey serial not null primary key, name varchar(50) );
> 
> insert into mytab1 (name)      values ('aaa')
>      returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
>                                   then setval('mytab1_pkey_seq',pkey,true)
>                                   else 0
>                              end );
> 
> insert into mytab1 (pkey,name) values (100,'bbb')
>      returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
>                                   then setval('mytab1_pkey_seq',pkey,true)
>                                   else 0
>                              end );
> 
> insert into mytab1 (name)      values ('ccc')
>      returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
>                                   then setval('mytab1_pkey_seq',pkey,true)
>                                   else 0
>                              end );
> 
> insert into mytab1 (pkey,name) values (50,'ddd')
>      returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
>                                   then setval('mytab1_pkey_seq',pkey,true)
>                                   else 0
>                              end );
> 
> insert into mytab1 (name)      values ('eee')
>      returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
>                                   then setval('mytab1_pkey_seq',pkey,true)
>                                   else 0
>                              end );
> 
> select * from mytab1 order by name;
> 
> =====
> 
> SELECT output:
> 
>   pkey | name
> ------+------
>      1 | aaa
>    100 | bbb
>    101 | ccc
>     50 | ddd
>    102 | eee
> (5 rows)
> 
> 
> 
> 
> PostgreSQL rocks!
> 
> Seb
>