Sample Header Ad - 728x90

Restarting identity columns in Postgresql

15 votes
2 answers
28805 views
For serial columns used in Postgresql < 10, we manage the sequence by its name. We were able to reset a sequence with:
SELECT setval('table_id_seq', (SELECT MAX(id) FROM table));
From version 10, using identity columns, there is no need to use the sequence name. That's nice.
ALTER TABLE table ALTER COLUMN id RESTART WITH 1000;
How do I set the identity column to be the max(id) without knowing the sequence name? As far as I can see from the [ALTER TABLE syntax](https://www.postgresql.org/docs/current/sql-altertable.html) there is no way to have a subquery to compute the start of the sequence. I would like to write something like:
ALTER TABLE table ALTER COLUMN id RESTART WITH (SELECT MAX(id) FROM table);
Asked by jgrocha (395 rep)
Jun 1, 2021, 05:26 PM
Last activity: Jul 29, 2025, 04:16 PM