Restarting identity columns in Postgresql

For serial columns used in Postgresql 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);


You can do the same with identity columns - they also use a sequence.

You can use pg_get_serial_sequence() to get the name of the sequence:

SELECT setval(pg_get_serial_sequence('the_table', 'id'), coalesce(MAX(id), 1))
from the_table;

The above works for serial and identity columns the same way.

StackExchange Database Administrators Q#292617, answer score: 20