PostgreSQL 2200H Error: Causes and Solutions Complete Guide

PostgreSQL Error 2200H: sequence generator limit exceeded

PostgreSQL error 2200H (sequence_generator_limit_exceeded) occurs when a sequence object has reached its defined maximum (or minimum) value and can no longer generate new numbers. This most commonly strikes SERIAL or SMALLSERIAL columns in high-volume tables, and it will bring your inserts to a grinding halt until resolved.

---

Top 3 Causes

1. INTEGER-based SERIAL column exhausted

The SERIAL type uses a 32-bit integer sequence with a maximum of 2,147,483,647. High-frequency inserts — even with frequent deletes — consume sequence values fast because rolled-back transactions still burn sequence numbers.

-- Check how close your sequence is to the limit
SELECT
    sequencename,
    last_value,
    max_value,
    ROUND((last_value::NUMERIC / max_value::NUMERIC) * 100, 2) AS usage_pct
FROM pg_sequences
WHERE sequencename = 'your_table_id_seq';

2. NO CYCLE default causes hard stop

By default, sequences are created with NO CYCLE, meaning once the max value is hit, every subsequent nextval() call throws error 2200H immediately.

-- Verify cycle setting on your sequence
SELECT sequencename, is_cycled, max_value, last_value
FROM pg_sequences
WHERE schemaname = 'public';

3. SMALLSERIAL used on a growing table

SMALLSERIAL tops out at just 32,767 — dangerously low for any table that grows over time. It's often chosen by mistake for log or event tables where row counts can explode unexpectedly.

-- Identify SMALLSERIAL / smallint sequences near their limit
SELECT
    sequencename,
    last_value,
    max_value,
    CASE WHEN max_value = 32767 THEN 'SMALLSERIAL ⚠️' ELSE 'OK' END AS type_warning
FROM pg_sequences
ORDER BY (last_value::NUMERIC / max_value::NUMERIC) DESC;

---

Quick Fix Solutions

Step 1 — Emergency patch: extend the sequence range

-- Immediately raise the max value to BIGINT range
ALTER SEQUENCE your_table_id_seq MAXVALUE 9223372036854775807;

Step 2 — Permanent fix: migrate column to BIGINT

-- Upgrade column type from INTEGER to BIGINT
ALTER TABLE your_table ALTER COLUMN id TYPE BIGINT;

-- For new tables, always use BIGSERIAL
CREATE TABLE better_table (
    id BIGSERIAL PRIMARY KEY,
    payload JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Modern alternative with IDENTITY columns (PostgreSQL 10+)
CREATE TABLE modern_table (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Step 3 — Resync sequence after data migration

-- Resync sequence to current max id to avoid conflicts
SELECT setval('your_table_id_seq', (SELECT MAX(id) FROM your_table));

---

Prevention Tips

1. Enforce BIGSERIAL as your team standard

Ban SERIAL and SMALLSERIAL in your DDL standards. Add a linting rule to your CI/CD pipeline that rejects any CREATE TABLE statement using those types. BIGSERIAL or BIGINT GENERATED ALWAYS AS IDENTITY should be the non-negotiable default for every primary key.

2. Monitor sequence usage proactively

Set up a monitoring job that alerts you when any sequence crosses 80% usage. Plug the query below into your monitoring stack (Prometheus, Datadog, pgBadger, etc.) and fire a Slack or PagerDuty alert before you hit the wall.

-- Sequences over 80% consumed — add this to your monitoring
SELECT
    schemaname || '.' || sequencename AS seq,
    last_value,
    max_value,
    ROUND((last_value::NUMERIC / max_value::NUMERIC) * 100, 2) AS pct_used
FROM pg_sequences
WHERE NOT is_cycled
  AND (last_value::NUMERIC / max_value::NUMERIC) >= 0.8
ORDER BY pct_used DESC;

---

Related Errors

Code	Name	Relationship
22003	numeric_value_out_of_range	Fires when the sequence value overflows the column's data type
23505	unique_violation	Occurs when a CYCLEd sequence reuses a value already in a unique column

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.