ALTER SEQUENCE RESTART WITH in PostgreSQL — Examples

Aug 17, 2025 / Jul 29, 2026 · 7 min read · postgresql sql database database-maintenance postgres-sequences postgresql-scripting dba-tips database-tuning pg_class ·

Share on:

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

Running this query does not restart anything by itself. It prints a list of ALTER SEQUENCE ... RESTART; statements as plain text rows — nothing in the database changes until those statements are copied out and executed separately. That two-step design is deliberate: it gives you a chance to read every affected sequence name before any counter actually moves, rather than firing a single opaque command that touches the whole catalog at once.

Breakdown & Key Points

Breakdown of the SQL Query

System Catalog – pg_class
- PostgreSQL stores metadata about objects (tables, indexes, sequences) in system catalogs.
- pg_class contains details about relations, including sequences.
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.
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.

relkind is not unique to sequences — the same column carries 'r' for an ordinary table, 'i' for an index, 'v' for a view, and 'm' for a materialized view, among others. Filtering on 'S' is what narrows a query against pg_class, which otherwise returns every relation in the database, down to sequences alone.

Gotchas in the Generated Script

The query above has two limitations worth knowing before running it against anything but a small, single-schema database:

No schema qualification. relname is the bare sequence name, not a schema-qualified one. On a database with sequences of the same name living in different schemas — a common pattern in multi-tenant designs — the generated ALTER SEQUENCE statement is ambiguous, and PostgreSQL resolves it against whichever schema sits first on the current search_path, which may not be the sequence you meant to restart.
No identifier quoting. Sequence names created with mixed case or a reserved word need double-quoting to run correctly. The bare string concatenation in the sample query does not add that quoting, so a sequence like "Order_Seq" would generate an invalid, unquoted statement.

A schema-safe variant closes both gaps by joining pg_class to pg_namespace and wrapping each identifier in quote_ident():

1SELECT
2    'ALTER SEQUENCE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' RESTART;'
3FROM
4    pg_class c
5    JOIN pg_namespace n ON n.oid = c.relnamespace
6WHERE
7    c.relkind = 'S'
8    AND n.nspname NOT IN ('pg_catalog', 'information_schema');

quote_ident() adds double-quotes only when a name actually needs them — an all-lowercase, non-reserved identifier passes through unchanged, so the output stays readable for the common case while still being correct for the edge case.

How to Use It

Run the query to generate the SQL commands.
Copy the output, which will be a list of ALTER SEQUENCE ... RESTART; statements.
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.

Running the generated statements requires ownership of the sequence, or superuser privileges — an application role with only SELECT/INSERT grants on the table gets a permission error on the ALTER SEQUENCE step, not a silent skip. Each ALTER SEQUENCE ... RESTART also takes a brief lock on the sequence object itself (not on the table), which is normally fast enough to be invisible but is still worth running during a low-traffic window on a sequence backing a high-throughput insert path, since concurrent nextval() calls against that sequence will wait for the lock to clear.

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.
Identity columns: A column declared GENERATED ALWAYS AS IDENTITY still has an ordinary sequence behind it, and the direct ALTER SEQUENCE ... RESTART shown here works against it. The documented path for identity columns specifically is ALTER TABLE table_name ALTER COLUMN column_name RESTART WITH value, which keeps the operation expressed in terms of the column rather than the sequence's generated name — useful when the sequence name itself isn't one you'd want to type from memory.
Read before you restart: The pg_sequences view (added in PostgreSQL 10) reports each sequence's current last_value and configuration in one query. Checking it before and after running a batch of restarts confirms the values actually landed where you expected, without relying on the generated script's own output as the only evidence.

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;

setval() takes an optional third argument, is_called, that defaults to true — meaning the next nextval() call returns the value passed in plus one. On a table that has just been truncated and re-loaded, that default is exactly what you want: set the sequence to the current maximum ID, and the next inserted row picks up from there. The one edge case worth remembering is a genuinely empty table: COALESCE(MAX(id), 1) returns 1 because MAX(id) on zero rows is NULL, but if you want the very next INSERT to actually receive 1 rather than 2, call setval() with is_called set to false explicitly.

Version Compatibility

The bare RESTART form used in the primary script — restarting a sequence back to its defined start value without specifying a number — is the same syntax already noted for the PostgreSQL 8.4 through 13 range covered above; the statement shape used in the generated script does not change within that range regardless of the exact point release.

The schema-aware variant in this post depends on pg_namespace, a catalog that has been part of PostgreSQL for as long as schemas themselves have existed, so it carries no additional version restriction beyond what pg_class itself requires. The quote_ident() function used to make identifier quoting safe is a long-standing built-in, not a recent addition.

Where version matters more is on the identity-column side. Columns declared GENERATED ... AS IDENTITY, together with the ALTER TABLE ... ALTER COLUMN ... RESTART WITH syntax that targets them directly, are a newer, SQL-standard-aligned alternative to the SERIAL pattern used throughout this post's examples. On a database still using SERIAL columns exclusively, the direct ALTER SEQUENCE approach shown here is the only available path, because SERIAL never formally links the column to its sequence in the catalog the way an identity column does.

Best Practices

Generate before you execute — run the SELECT first and read the output; never chain the generation query directly into execution without a human reading the statement list.
Prefer the schema-qualified variant on multi-schema databases — the bare relname version is fine for a single-schema database but risks resolving against the wrong schema anywhere else.
Pair every restart with a MAX(id) check — restarting a sequence to 1 on a table that already holds data is the single most common way this script causes a duplicate-key error in production.
Run during low-traffic windows on hot sequences — ALTER SEQUENCE briefly locks the sequence object itself; that's normally invisible but still worth avoiding on a high-insert-rate table at peak load.
Confirm ownership or superuser access first — a role without ALTER rights on the sequence fails the statement outright rather than silently doing nothing.
Verify with pg_sequences after the fact — checking last_value post-restart is cheap insurance against a statement that silently targeted the wrong object.