numeric value out of range

Category: Data ExceptionVersions: All Postgres versions

What this means

A numeric value (integer, bigint, numeric, real, double precision) exceeded the storage range of its target type. The overflow is detected in the executor type input/conversion function before the value is written to the heap.

Why it happens

1Inserting or computing a value larger than the maximum for INTEGER (2,147,483,647) or SMALLINT (32,767)
2SERIAL or BIGSERIAL sequence exhausted (wrapped past the maximum value)
3Arithmetic overflow in a computation (e.g., very large multiplication)
4Casting a NUMERIC or DOUBLE PRECISION value that is too large to INTEGER

How to reproduce

An integer column receives a value exceeding the INTEGER maximum.

trigger — this will ERROR

CREATE TABLE counters (id SERIAL PRIMARY KEY, val INTEGER);

INSERT INTO counters (val) VALUES (2147483648); -- INT max is 2147483647

ERROR: integer out of range

Fix 1: Use BIGINT for larger integer ranges

When the value legitimately exceeds INTEGER range (max ~2.1 billion).

fix

ALTER TABLE counters ALTER COLUMN val TYPE BIGINT;

-- Or change a SERIAL to BIGSERIAL at table creation:
CREATE TABLE counters (
  id  BIGSERIAL PRIMARY KEY,
  val BIGINT
);

Why this works

BIGINT uses 8 bytes and supports values up to 9,223,372,036,854,775,807. ALTER TABLE ALTER COLUMN TYPE rewrites the table heap to store the wider type. For columns that are not near exhaustion, this is a proactive schema widening.

Fix 2: Use NUMERIC for arbitrary precision

When exact precision is required and values may be arbitrarily large.

fix

ALTER TABLE counters ALTER COLUMN val TYPE NUMERIC;

Why this works

NUMERIC stores numbers with user-specified precision and scale using a variable-length representation. It supports values up to 131,072 digits before the decimal point. The tradeoff is slower arithmetic compared to native integer types.

What not to do

✗

Silently truncate overflow values with MOD or masking

Why it's wrong: Produces incorrect results that appear valid; data corruption is silent and may not be discovered until an audit.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

📚 Feature docs: https://www.postgresql.org/docs/current/datatype-numeric.html

🔧 Source ref: src/backend/utils/adt/int.c — int4in()

📖 Further reading: Numeric Types

Confidence assessment

✅ HIGH confidence

Stable and well-documented. Type ranges are fixed by the SQL standard and have not changed across Postgres versions. Edge case: floating-point overflow to Infinity does not raise 22003 for DOUBLE PRECISION; it silently stores Infinity.