Restarting identity columns in Postgresql

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply