agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Sebastien FLAESCH <sf@4js.com>
To: pgsql-sql@lists.postgresql.org
Subject: Re: Get last generated serial sequence and set it up when explicit value is used
Date: Wed, 25 Nov 2020 12:32:57 +0100
Message-ID: <00531c42-840c-3dab-e149-4d1cb2b43bf5@4js.com> (raw)
In-Reply-To: <d375a643-eb10-d476-ee38-060a9d5e5d1e@4js.com>
References: <fc65c19a-7701-13c8-b202-77bfc8fbf995@4js.com>
<d375a643-eb10-d476-ee38-060a9d5e5d1e@4js.com>
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
>
view thread (7+ messages)
Message-ID: <00531c42-840c-3dab-e149-4d1cb2b43bf5@4js.com>
Permalink: ../00531c42-840c-3dab-e149-4d1cb2b43bf5@4js.com/
Also on: postgresql.org/message-id/00531c42-840c-3dab-e149-4d1cb2b43bf5@4js.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sf@4js.com, pgsql-sql@lists.postgresql.org
Subject: Re: Get last generated serial sequence and set it up when explicit value is used
In-Reply-To: <00531c42-840c-3dab-e149-4d1cb2b43bf5@4js.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox