Restarting identity columns in Postgresql

Question

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

Answer 1

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.