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 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);