Restarting All PostgreSQL Sequences with SQL Script

Database administrators often face challenges with PostgreSQL sequences, especially after data has been deleted or when tables have been re-imported. Sequences drive the generation of auto-incrementing values (like primary keys using SERIAL or BIGSERIAL), and sometimes they need to be reset or restarted.

This guide covers a simple SQL script that restarts all PostgreSQL sequences across your database, making it easier to bring sequences back in sync with your table data.

Sample Code from Command Line

1-- Restarts all PostgreSQL sequences
2SELECT
3    'ALTER SEQUENCE ' || relname || ' RESTART;'
4FROM
5    pg_class
6WHERE
7    relkind = 'S';

Breakdown & Key Points

Breakdown of the SQL Query

1. System Catalog – pg_class

   • PostgreSQL stores metadata about objects (tables, indexes, sequences) in system catalogs.
   • pg_class contains details about relations, including sequences.

2. Filtering with relkind = 'S'

   • relkind identifies the type of object stored.
   • 'S' means sequence. This filter ensures we only target sequences, not tables or indexes.

3. Generating ALTER SEQUENCE Statements

   • The query dynamically builds commands like:

     1ALTER SEQUENCE my_table_id_seq RESTART; 
     

   • Each row returned is a ready-to-run SQL command that resets a specific sequence.

How to Use It

1. Run the query to generate the SQL commands.
2. Copy the output, which will be a list of ALTER SEQUENCE ... RESTART; statements.
3. Execute the generated SQL statements to reset all sequences.

Note: Restarting a sequence sets its counter back to the initial value (default 1 unless specified otherwise). If your target table already has data, you may need to adjust the sequence with RESTART WITH <number> so it doesn’t cause primary key conflicts.

Key Points and Insights

• Database Versions: Works across PostgreSQL 8.4 to 13, ensuring compatibility with most environments.
• Re-imported Data Fix: Useful after bulk inserts, imports, or restores, where sequence values fall out of sync with table data.
• Alternative Reset: Instead of restarting, you can use:

  1ALTER SEQUENCE my_table_id_seq RESTART WITH <next_id>; 
  

  to align a sequence with the current maximum value in its table.
• DBA Tip: Always double-check existing data before restarting, to avoid duplicate key errors.

Example Use Case

Imagine you truncated or reloaded data into a table with a SERIAL primary key. The table’s actual data might have IDs up to 500, but the sequence may still be ready to generate 1. Restarting it would cause conflicts. In such cases, combine this restart script with sequence alignment commands like:

1SELECT setval(pg_get_serial_sequence('table_name', 'id'), COALESCE(MAX(id), 1)) FROM table_name;

References

• PostgreSQL Documentation – ALTER SEQUENCE

• PostgreSQL System Catalogs – pg_class