After some administrative operations you might need to Reset key sequence in PostgreSQL. During modernization journey, team PostgreSQL designed shortcuts and improved reset key sequence workflow.

TL;DR: ALTER SEQUENCE sequence_name RESET

Resetting Key Sequence in PostgreSQL

Sequence is the main object that keeps up with row identifiers. When you create a table with a sequence you can set it’s starting value, max value, increment etc. During runtime, you may need to change it’s current value. Altering a sequence is the solution for this.

SELECT MAX(id_column) FROM my_table;
ALTER SEQUENCE sequence_name RESET WITH max_value +1 

In previous version of PostgreSQL you may need to call some prepared statements, LOCK table to reset the sequence.

To learn more about ALTER SEQUENCE statement, you can check PostgreSQL documentation from this link. PostgreSQL’ documentation is a clear product documentation, readable and well organized.

Prerequirements to Reset Key Sequence

In new versions, there is no prereq to reset key sequence in PostgreSQL. You can directly run ALTER SEQUENCE statement to change it’s actual value. It is effective immediately.

Please leave a comment below, if you anything to add or if you have any questions. I’ll be happy to meet you.