5

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

1 Answer 1

9

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.