(opens in a new tab)

Postgres Reset Sequence: Fix Out-of-Sync IDs with setval

August 29, 2026 by Chat2DB Team

You restore a dump, copy rows in with explicit IDs, or truncate a table for tests — and the next ordinary INSERT fails with:

ERROR:  duplicate key value violates unique constraint "users_pkey"
DETAIL:  Key (id)=(42) already exists.

Nothing is wrong with the table. The sequence feeding the id column is simply behind: it still thinks the next number is 42 while the table already contains rows up to 9,000. This guide shows how sequences work, how to reset one correctly with setval(), how to find which sequence a column uses, and how to fix every sequence in a database at once.

How sequences produce your IDs

A sequence is a tiny special relation that hands out numbers. Both serial columns and identity columns (GENERATED ... AS IDENTITY, the modern form) are backed by one:

CREATE TABLE users (
    id   bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name text NOT NULL
);

Three functions drive it:

SELECT nextval('users_id_seq');   -- advance and return the next value
SELECT currval('users_id_seq');   -- last value THIS session got (errors before first nextval)
SELECT setval('users_id_seq', 9000);  -- set the current value

Two properties explain every symptom:

• The sequence never looks at the table. Insert id = 500 explicitly and the sequence still sits wherever it was — it will happily hand out 500 again later.
• nextval is non-transactional. A rolled-back insert still consumed its number. Gaps are normal and harmless; sequences guarantee uniqueness, not contiguity.

The fix: setval to the table's maximum

SELECT setval('users_id_seq', (SELECT max(id) FROM users));

The next nextval returns max(id) + 1. Two refinements make this robust:

-- Works even when the table is EMPTY:
SELECT setval(
  'users_id_seq',
  COALESCE((SELECT max(id) FROM users), 1),
  (SELECT max(id) IS NOT NULL FROM users)   -- is_called flag
);

The third argument, is_called, decides whether the given value has already been used. With true (default), the next value is value + 1; with false, the next value is value itself. That's why the empty-table case passes false — so the first row gets 1, not 2.

For identity columns there's an equivalent DDL spelling:

ALTER TABLE users ALTER COLUMN id RESTART WITH 9001;
-- or for any sequence:
ALTER SEQUENCE users_id_seq RESTART WITH 9001;

RESTART WITH needs a literal number, which is why the setval(... max(id) ...) form is what scripts use.

Finding the sequence behind a column

Don't guess the table_column_seq name — ask PostgreSQL:

SELECT pg_get_serial_sequence('users', 'id');
--  public.users_id_seq

This works for both serial and identity columns, and returns the schema-qualified name safely. Combined with setval, the canonical one-liner to repair any table is:

SELECT setval(
  pg_get_serial_sequence('users', 'id'),
  COALESCE((SELECT max(id) FROM users), 1),
  (SELECT max(id) IS NOT NULL FROM users)
);

To inspect a sequence's state (PostgreSQL 10+):

SELECT seqrelid::regclass AS sequence, seqstart, seqincrement, seqmax
FROM   pg_sequence
WHERE  seqrelid = 'users_id_seq'::regclass;
 
SELECT last_value, is_called FROM users_id_seq;  -- current position

Resetting every sequence in the database

After a full data migration, fix all sequences at once by generating the statements:

SELECT format(
  'SELECT setval(%L, COALESCE((SELECT max(%I) FROM %I.%I), 1), '
  || '(SELECT max(%I) IS NOT NULL FROM %I.%I));',
  s.seq_name, s.col, s.sch, s.tbl, s.col, s.sch, s.tbl
) AS fix_sql
FROM (
  SELECT n.nspname AS sch, c.relname AS tbl, a.attname AS col,
         pg_get_serial_sequence(n.nspname||'.'||c.relname, a.attname) AS seq_name
  FROM   pg_class c
  JOIN   pg_namespace n ON n.oid = c.relnamespace
  JOIN   pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0
  WHERE  c.relkind = 'r'
  AND    pg_get_serial_sequence(n.nspname||'.'||c.relname, a.attname) IS NOT NULL
) s;

Run the query, then execute the rows it returns (in psql: \gexec right after it). Every serial and identity column in the database is now aligned with its table.

TRUNCATE, deletes, and starting over

DELETE FROM users does not reset the sequence — by design, old IDs should not be reused. If you're wiping a table and want IDs to start from 1 again:

TRUNCATE users RESTART IDENTITY;           -- truncate + reset in one statement
TRUNCATE users RESTART IDENTITY CASCADE;   -- include FK-referencing tables

Without RESTART IDENTITY, TRUNCATE leaves sequences untouched (CONTINUE IDENTITY is the default).

Why sequences drift in the first place

1. Restores and imports with explicit IDs. COPY and INSERT ... (id, ...) bypass the default. (A full pg_dump restore is safe — dumps include setval calls — but selective/partial restores and hand-rolled ETL are not.)
2. GENERATED BY DEFAULT identity columns let applications supply IDs, silently outrunning the sequence. GENERATED ALWAYS prevents this: explicit IDs then require OVERRIDING SYSTEM VALUE, which makes the drift deliberate rather than accidental.
3. Logical replication / failover where the subscriber's sequences don't advance with the data (sequence changes aren't replicated logically before PG17's sequence sync work).

If you control the schema, prefer GENERATED ALWAYS AS IDENTITY — it turns the most common cause into an explicit, visible choice.

Common errors decoded

• duplicate key value violates unique constraint right after a migration → sequence behind the table; run the setval one-liner.
• currval of sequence "users_id_seq" is not yet defined in this session → currval only works after this session called nextval. To read the position without consuming a value, query last_value from the sequence, or use pg_sequence_last_value('users_id_seq').
• nextval: reached maximum value of sequence → an integer (serial) sequence hit 2,147,483,647. Migrate the column and sequence to bigint: see our bigint vs int guide.
• Sequence resets "not sticking" after crash → setval is WAL-logged and durable; what you're likely seeing is another writer calling nextval in between. Note that unlogged sequences (attached to unlogged tables) reset after a crash by design.

Concurrency: is setval(max(id)) safe on a live table?

Mostly. setval itself is atomic, but between your max(id) read and the setval call another transaction could insert a higher explicit ID. On a busy table, either take a short lock (LOCK TABLE users IN EXCLUSIVE MODE inside the fixing transaction — remember setval is still non-transactional, so do this in a maintenance window) or simply set the sequence generously ahead: setval(seq, max(id) + 1000). Gaps cost nothing; collisions cost pages.

Running these repair statements is quicker with a client that shows you tables, sequences and their current values side by side. Chat2DB (opens in a new tab) does — and its AI assistant will generate the bulk-reset SQL above for your actual schema. There's also a zero-install web version at app.chat2db.ai (opens in a new tab).

FAQ

How do I reset a sequence to 1? ALTER SEQUENCE users_id_seq RESTART WITH 1; — but only on an empty table, otherwise the next inserts collide with existing rows. For "empty the table and reset", use TRUNCATE users RESTART IDENTITY.

What's the difference between setval and ALTER SEQUENCE RESTART? setval is a function — callable with a computed value like max(id), effective immediately, and non-transactional. ALTER SEQUENCE ... RESTART is DDL — it takes a literal, is transactional (rolls back with the transaction), and requires ownership of the sequence.

Do sequences guarantee gap-free numbering for invoices? No — rollbacks, crashes and caching all leave gaps. For legally gap-free numbers, use a counter table updated in the same transaction as the insert (UPDATE counters SET n = n + 1 ... RETURNING n), accepting the serialization that implies.