🚨 PostgreSQL Error Guide: 'integer out of range' — Fix and Prevent

Overview

PostgreSQL raises this when a value exceeds the range of the integer type it must fit into — most dangerously when an integer (int4) primary key sequence passes 2,147,483,647:

ERROR:  integer out of range

It also appears from arithmetic overflow (a SUM or multiplication exceeding the type’s range) and from inserting a literal larger than the column’s type allows. int4 (integer) tops out at 2,147,483,647; int2 (smallint) at 32,767; int8 (bigint) at roughly 9.2 quintillion.

Symptoms

• Inserts into a table with a serial/integer primary key suddenly fail once the sequence crosses 2.1 billion.
• An aggregate query (SUM, count(*) * ...) fails on a large dataset.
• An INSERT/UPDATE fails when application code computes a value beyond int4 range.
• A smallint column rejects a value over 32,767.
• The failure is abrupt and total — every insert on a maxed-out serial PK fails, causing an outage.

Common Root Causes

• serial/integer primary key exhaustion — the sequence has climbed past 2,147,483,647; this is the classic “we ran out of ids” outage. Note the sequence advances on failed/rolled-back inserts too, so exhaustion can arrive before row count would suggest.
• Arithmetic overflow — SUM(int_column) or a * b over many rows exceeds int4, since the result keeps the input type.
• smallint column given a value over 32,767 (e.g. a year-count, port, or quantity that grew).
• A literal or parameter larger than the destination integer type.
• Type mismatch in a computed/generated column that stays integer while inputs grew.
• Epoch/millisecond timestamps stored as integer overflowing (milliseconds since epoch exceed int4).

Diagnostic Workflow

First determine whether this is sequence exhaustion or arithmetic overflow — the fixes differ sharply.

For a suspected primary-key sequence, check how close it is to the int4 ceiling:

-- current value vs the int4 max (2,147,483,647)
SELECT last_value, 2147483647 - last_value AS headroom
FROM users_id_seq;

Find the sequence backing a column if you don’t know its name:

SELECT pg_get_serial_sequence('users', 'id');

Identify which columns in the database are still integer/smallint primary keys and how full they are (proactive audit):

SELECT c.relname AS table, a.attname AS column, t.typname AS type
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_type t ON t.oid = a.atttypid
JOIN pg_constraint con ON con.conrelid = c.oid AND a.attnum = ANY(con.conkey)
WHERE con.contype = 'p' AND t.typname IN ('int4', 'int2') AND c.relkind = 'r';

For arithmetic overflow, reproduce with an explicit bigint cast to confirm the result is genuinely large:

SELECT SUM(amount::bigint) FROM ledger;   -- succeeds where SUM(amount) overflowed

Check the column type directly:

\d+ users

Example Root Cause Analysis

A high-volume events service began failing every insert with integer out of range. The events table had id integer backed by events_id_seq. Checking the sequence:

SELECT last_value, 2147483647 - last_value AS headroom FROM events_id_seq;

 last_value | headroom
------------+----------
 2147483647 |        0

The int4 sequence was fully exhausted — every new insert tried to allocate 2,147,483,648, which doesn’t fit int4. The row count was under 2 billion because failed inserts and deleted rows had still consumed sequence values. This is a hard outage: no new events can be written.

The fix is to migrate the column and sequence to bigint. On a large hot table this must avoid a full-table rewrite lock, so the safe pattern is a new column backfilled online, then swapped — but the direct approach (used here in a maintenance window) is:

-- widen the column (this rewrites the table; schedule it)
ALTER TABLE events ALTER COLUMN id TYPE bigint;
-- the owned sequence is already bigint-capable; ensure it is
ALTER SEQUENCE events_id_seq AS bigint;

For zero-downtime, the online approach adds a bigint shadow column, backfills in batches, keeps it in sync with a trigger, then swaps the primary key — but the root fix is the same: int4 was too small for the id space.

Prevention Best Practices

• Use bigint/bigserial (or an identity column typed bigint) for any primary key that could ever exceed ~2 billion rows or ids — the extra 4 bytes is cheap insurance against an outage.
• Audit existing integer primary keys and alert when a sequence crosses ~70-80% of the int4 max, well before exhaustion.
• Remember sequences advance on rolled-back and failed inserts, so plan headroom against sequence value, not row count.
• Cast to bigint inside aggregates over large datasets: SUM(col::bigint), count(*)::bigint * n.
• Size smallint columns only where the domain is truly bounded (< 32,767) and unlikely to grow.
• Store millisecond/epoch timestamps as bigint or a proper timestamptz, never integer.

Quick Command Reference

-- How close is a sequence to the int4 ceiling?
SELECT last_value, 2147483647 - last_value AS headroom FROM users_id_seq;

-- Find the sequence for a serial column
SELECT pg_get_serial_sequence('users', 'id');

-- Reproduce/avoid arithmetic overflow with a bigint cast
SELECT SUM(amount::bigint) FROM ledger;

-- Widen a column to bigint (rewrites the table — schedule a window)
ALTER TABLE events ALTER COLUMN id TYPE bigint;
ALTER SEQUENCE events_id_seq AS bigint;

-- Column type
\d+ events

Conclusion

integer out of range splits into two very different problems. Arithmetic overflow is a quick fix — cast to bigint inside the aggregate or expression. Primary-key sequence exhaustion is a genuine outage: the int4 id space is full and no rows can be inserted until the column is migrated to bigint. The lesson is to default new primary keys to bigint/bigserial and to alert on sequence headroom (not row count, since failed inserts consume ids) long before the ceiling. Widening an int4 PK to bigint rewrites the table, so plan the migration — online with a shadow column for hot tables, or a scheduled window otherwise.