EC
22003
PostgreSQLERRORCommonData ExceptionHIGH confidence

numeric value out of range

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
  1. 1Inserting or computing a value larger than the maximum for INTEGER (2,147,483,647) or SMALLINT (32,767)
  2. 2SERIAL or BIGSERIAL sequence exhausted (wrapped past the maximum value)
  3. 3Arithmetic overflow in a computation (e.g., very large multiplication)
  4. 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
trigger — this will error
CREATE TABLE counters (id SERIAL PRIMARY KEY, val INTEGER);

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

expected output

ERROR:  integer out of range

Fix 1

Use BIGINT for larger integer ranges

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

Use BIGINT for larger integer ranges
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 When exact precision is required and values may be arbitrarily large.

Use NUMERIC for arbitrary precision
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

Produces incorrect results that appear valid; data corruption is silent and may not be discovered until an audit.

Sources
Official documentation ↗

src/backend/utils/adt/int.c — int4in()

Numeric Types ↗

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors