EC
2200H
PostgreSQLERRORCommonData ExceptionHIGH confidence

sequence generator limit exceeded

Production Risk

High: once the sequence is exhausted every INSERT to the table fails until the sequence is altered or the key type is widened.

What this means

SQLSTATE 2200H is raised when a sequence generator has reached its maximum (or minimum for descending sequences) value and cannot produce another value without cycling, and the sequence was created with NO CYCLE.

Why it happens
  1. 1A SERIAL, BIGSERIAL, or manually created SEQUENCE has exhausted all values in its range
  2. 2nextval() called on a sequence with MAXVALUE already reached and NO CYCLE set
How to reproduce

Sequence exhausted — all values consumed.

trigger — this will error
trigger — this will error
CREATE SEQUENCE tiny_seq MINVALUE 1 MAXVALUE 3 NO CYCLE;
SELECT nextval('tiny_seq'); -- 1
SELECT nextval('tiny_seq'); -- 2
SELECT nextval('tiny_seq'); -- 3
SELECT nextval('tiny_seq'); -- ERROR: 2200H

expected output

ERROR:  nextval: reached maximum value of sequence "tiny_seq" (3)

Fix 1

Migrate the primary key column from INTEGER/SERIAL to BIGINT/BIGSERIAL

WHEN When an integer PK sequence is nearing exhaustion.

Migrate the primary key column from INTEGER/SERIAL to BIGINT/BIGSERIAL
ALTER TABLE orders ALTER COLUMN id TYPE BIGINT;
ALTER SEQUENCE orders_id_seq MAXVALUE 9223372036854775807;

Why this works

BIGINT sequences have a range of ~9.2 quintillion and are practically inexhaustible for typical workloads.

Fix 2

Reset or recycle the sequence with CYCLE

WHEN When sequence values are not used as unique identifiers and cycling is acceptable.

Reset or recycle the sequence with CYCLE
ALTER SEQUENCE my_seq CYCLE RESTART;

Why this works

WITH CYCLE causes the sequence to wrap back to MINVALUE after reaching MAXVALUE. Use only when duplicate values are safe.

What not to do

✕ Enable CYCLE on a primary key sequence

Cycling causes sequence values to repeat, which violates uniqueness on primary key columns and causes 23505 errors.

Sources

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors