12

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

19

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.

1
  • In case others have my same issue maybe there's an answer for them: This didn't work for me, because I always get that the sequence does not exist. When I SELECT * FROM information_schema.sequences , sure enough, there are no sequences anywhere to be found (even though I have a primary key as an identity column), so I try manually creating the sequence and assigning it to the table, and I get an error that it's an IDENTITY column (so I can't assign a manually created sequence I'm assuming?) So, I just gave up and went with a code based solution to do this instead of doing it directly in SQL. Aug 30 at 5:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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