How can I automatically fix my Postgres sequence?

Question

I want to update a sequence in Postgres, which I can do semi-manually, like so:

SELECT MAX(id) as highest_id FROM users;
ALTER SEQUENCE users_id_seq RESTART WITH 11071;

In this case I have to take the result of the first query, which turns out to be 11070, and insert it into the next query, incremented by 1. I'd rather have a single query that does all of this in one fell swoop.

The "two fell swoops" approach would be like so, if it worked, but this fails:

ALTER SEQUENCE users_id_seq RESTART WITH (SELECT MAX(id) as highest_id FROM users);
ALTER SEQUENCE users_id_seq INCREMENT BY 1;

Even better would be if I could use + 1 in the first ALTER SEQUENCE statement and skip the second one.

Is there any way to fix this so it works? (Either as two steps or one, but without manual intervention by me.)

richyen · Accepted Answer · 2021-01-06 04:50:22Z

You can easily do this with:

SELECT setval('users_id_seq',(SELECT max(id) FROM users));

This sets the sequence to the current value, so that when you call nextval(), you'll get the next one:

edb=# create table foo (id serial primary key, name text);
CREATE TABLE
edb=# insert into foo values (generate_series(1,10000),'johndoe');
INSERT 0 10000
edb=# select * from foo_id_seq ;
 last_value | log_cnt | is_called 
------------+---------+-----------
          1 |       0 | f
(1 row)

edb=# select setval('foo_id_seq',(SELECT max(id) FROM foo));
 setval 
--------
  10000
(1 row)

edb=# select * from foo_id_seq ;
 last_value | log_cnt | is_called 
------------+---------+-----------
      10000 |       0 | t
(1 row)

edb=# insert into foo values (default,'bob');
INSERT 0 1
edb=# select * from foo order by id desc limit 1;
  id   | name 
-------+------
 10001 | bob
(1 row)

Collectives™ on Stack Overflow

How can I automatically fix my Postgres sequence?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related