agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Sebastien FLAESCH <sf@4js.com>
To: pgsql-sql@lists.postgresql.org
Subject: Get last generated serial sequence and set it up when explicit value is used
Date: Thu, 19 Nov 2020 20:21:39 +0100
Message-ID: <fc65c19a-7701-13c8-b202-77bfc8fbf995@4js.com> (raw)
Hi all!
Using SERIAL or BIGSERIAL column, I try to find a smart solution to
do the following when an INSERT is done:
1) Retrieve the last generated sequence, so the program can use it.
2) Setup the underlying sequence, if an explicit value was used by
the INSERT statement.
So far I figured out the following by using the RETURNING clause...
Is this ok / legal / without risk? (when multiple users insert rows at the same time?)
test1=# create table table1 ( pkey serial not null primary key, name varchar(50) );
CREATE TABLE
test1=# insert into table1 (name) values ('aaaa') returning pkey, (select last_value from table1_pkey_seq);
pkey | last_value
------+------------
1 | 1
(1 row)
INSERT 0 1
test1=# insert into table1 (name) values ('aaaa') returning pkey, (select last_value from table1_pkey_seq);
pkey | last_value
------+------------
2 | 2
(1 row)
INSERT 0 1
test1=# insert into table1 (pkey,name) values (100,'aaaa') returning pkey, (select last_value from table1_pkey_seq);
pkey | last_value
------+------------
100 | 2
(1 row)
INSERT 0 1
I see 100 is > than 2, so reset the sequence:
test1=# select setval('table1_pkey_seq',101,false);
setval
--------
101
(1 row)
test1=# insert into table1 (name) values ('aaaa') returning pkey, (select last_value from table1_pkey_seq);
pkey | last_value
------+------------
101 | 101
(1 row)
INSERT 0 1
Any better way to do that in a single SQL statement?
Is it legal to use a subquery in a RETURNING clause?
Thanks!
Seb
view thread (7+ messages) latest in thread
Message-ID: <fc65c19a-7701-13c8-b202-77bfc8fbf995@4js.com>
Permalink: ../fc65c19a-7701-13c8-b202-77bfc8fbf995@4js.com/
Also on: postgresql.org/message-id/fc65c19a-7701-13c8-b202-77bfc8fbf995@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: <fc65c19a-7701-13c8-b202-77bfc8fbf995@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