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
>