PostgreSQL 2200H Error: Causes and Solutions Complete Guide

Source: Dev.to

PostgreSQL Error 2200H: Sequence Generator Limit Exceeded

PostgreSQL error code 2200H occurs when a sequence object reaches its defined MAXVALUE (or MINVALUE for descending sequences) and has no CYCLE option to wrap around. This is most commonly seen on tables using SERIAL (INT4) primary keys, which cap out at approximately 2.1 billion. Once the limit is hit, every subsequent INSERT attempting to use that sequence will fail immediately. SERIAL Column Hitting the INT4 Ceiling (~2.1 Billion)

SERIAL uses a 4-byte integer under the hood. High-volume systems — logging tables, event trackers, order systems — can exhaust this faster than expected. — Check how close your sequences are to their limit SELECT sequencename, last_value, max_value, ROUND((last_value::NUMERIC / max_value) * 100, 2) AS used_pct, (max_value - last_value) AS remaining FROM pg_sequences WHERE schemaname = ‘public’ ORDER BY used_pct DESC;

MAXVALUE Set Too Low

When a sequence is manually created with an artificially low MAXVALUE and NO CYCLE (the default), it will throw 2200H as soon as the cap is reached. — Example of a problematic sequence definition CREATE SEQUENCE bad_sequence START 1 INCREMENT 1 MAXVALUE 10000 — Way too low for production use NO CYCLE;

— Check a specific sequence definition SELECT * FROM pg_sequences WHERE sequencename = ‘bad_sequence’;

PostgreSQL sequences are non-transactional by design — rolled-back transactions do not return their consumed values. In retry-heavy applications or batch jobs, sequence values can be consumed far faster than actual committed rows suggest. — Demonstrate sequence consumption on rollback BEGIN; SELECT nextval(‘orders_order_id_seq’); — value consumed ROLLBACK; — The value is gone. nextval will skip it permanently.

— You can check current sequence value without advancing it SELECT last_value FROM orders_order_id_seq;

Fix 1: Expand the sequence’s MAXVALUE immediately (zero downtime) — Immediate relief with no table lock required ALTER SEQUENCE orders_order_id_seq MAXVALUE 9223372036854775807;

Fix 2: Change the column type to BIGINT — Upgrade the column and its backing sequence ALTER TABLE orders ALTER COLUMN order_id TYPE BIGINT; ALTER SEQUENCE orders_order_id_seq MAXVALUE 9223372036854775807;

Fix 3: Reset sequence to current max (emergency recovery) — If inserts are already failing, resync the sequence SELECT SETVAL( ‘orders_order_id_seq’, (SELECT MAX(order_id) FROM orders), true );

Fix 4: Migrate to IDENTITY column (recommended long-term) — Best practice for new tables in PostgreSQL 10+ CREATE TABLE orders_new ( order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id INT NOT NULL, created_at TIMESTAMP DEFAULT NOW() );

1. Monitor sequence usage proactively. SELECT sequencename, last_value, max_value, ROUND((last_value::NUMERIC / NULLIF(max_value,0)) * 100, 2) AS used_pct FROM pg_sequences WHERE (last_value::NUMERIC / NULLIF(max_value,0)) > 0.8;

2. Standardize on BIGINT GENERATED ALWAYS AS IDENTITY or UUID for all new tables. SERIAL as a legacy type. BIGINT identity columns give you ~9.2 quintillion values, while UUID eliminates the exhaustion problem entirely. — Preferred modern pattern CREATE TABLE events ( event_id UUID DEFAULT gen_random_uuid() PRIMARY KEY, payload JSONB, created_at TIMESTAMP DEFAULT NOW() );

23505 unique_violation — Can occur if CYCLE is enabled and reused values collide with existing primary keys. 55000 object_not_in_prerequisite_state — Raised when calling nextval() on an already-exhausted sequence without CYCLE. 📖 Want a more detailed guide? oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.