Subject: Re: Get last generated serial sequence and set it up when explicit
 value is used
From: Sebastien FLAESCH <sf@4js.com>
To: pgsql-sql@lists.postgresql.org
References: <fc65c19a-7701-13c8-b202-77bfc8fbf995@4js.com>
 <d375a643-eb10-d476-ee38-060a9d5e5d1e@4js.com>
Organization: Four Js Development Tools
Message-ID: <00531c42-840c-3dab-e149-4d1cb2b43bf5@4js.com>
Date: Wed, 25 Nov 2020 12:32:57 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.4.2
MIME-Version: 1.0
In-Reply-To: <d375a643-eb10-d476-ee38-060a9d5e5d1e@4js.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

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
>