A PostgreSQL sequence has reached its maximum value, preventing new ID generation. This occurs when SERIAL columns exhaust their 4-byte integer range (~2.1 billion). Fix by altering the sequence to BIGINT or using CYCLE mode.
In PostgreSQL, sequences are database objects that generate unique sequential numbers, commonly used for auto-incrementing primary keys. When a sequence reaches its maximum value limit (determined by its data type), it cannot generate new values and throws the "Sequence generator limit exceeded" error with code 2200H. This typically happens with 4-byte INTEGER sequences that hit their maximum of 2,147,483,647. The sequence is a separate generator object from the column itself, so the error occurs at the sequence level, not necessarily the table column.
Run this query to see sequence details and current value:
SELECT sequencename, data_type, last_value, max_value
FROM pg_sequences
WHERE schemaname = 'public';Note the data_type and max_value. If data_type is 'integer' or 'smallint', that is your bottleneck.
Convert the sequence to use 64-bit BIGINT (max value ~9.2 quintillion):
ALTER SEQUENCE sequence_name AS BIGINT;Then verify the change:
SELECT sequencename, data_type, max_value
FROM pg_sequences
WHERE sequencename = 'sequence_name';If the sequence is now BIGINT but the column is still INTEGER, the column must also be widened:
ALTER TABLE table_name
ALTER COLUMN column_name TYPE BIGINT;This ensures both the sequence and column can handle large values.
Verify that the sequence can now generate new values:
SELECT nextval('sequence_name');If no error occurs, the issue is resolved. Try inserting a test record to confirm end-to-end functionality.
For new tables, use BIGSERIAL instead of SERIAL:
-- OLD (limited to 2.1B rows)
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT
);
-- NEW (supports 9.2 quintillion rows)
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
name TEXT
);Alternatively, use SQL standard GENERATED ALWAYS AS IDENTITY:
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT
);The CYCLE option can be set on sequences to wrap around to the minimum value once maximum is reached (ALTER SEQUENCE sequence_name CYCLE), but this is rarely recommended for primary keys as it can create duplicate values. For tables expecting extremely high row counts (>2.1 billion), BIGINT should be used from inception. The sequence and column data types are independent; a BIGINT sequence can serve an INTEGER column and vice versa, but this mismatch is a source of bugs. Note that sequences are not transactional—once nextval() increments a sequence, that value is consumed even if the transaction rolls back. Monitor sequence exhaustion using: SELECT (last_value::float / max_value::float) * 100 as pct_used FROM pg_sequences WHERE sequencename = "sequence_name";
vacuum failsafe triggered
How to fix "vacuum failsafe triggered" in PostgreSQL
PANIC: could not write to file
How to fix PANIC: could not write to file in PostgreSQL
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL
ERROR 42501: must be owner of table
How to fix "must be owner of table" in PostgreSQL
trigger cannot change partition destination
How to fix "Trigger cannot change partition destination" in PostgreSQL