ERROR 22003: Numeric Value Out of Range in Postgres

Error Message#

ERROR: integer out of range
SQLSTATE: 22003

The wording shifts with the type and the operation, but the SQLSTATE stays 22003:

ERROR: smallint out of range
ERROR: bigint out of range
ERROR: value "9999999999" is out of range for type integer
ERROR: numeric field overflow
DETAIL: A field with precision 5, scale 2 must round to an absolute value less than 10^3.

PostgreSQL never clamps. Where MySQL in non-strict mode would quietly trim the value and move on, Postgres rejects the write. That is the safer behavior, and it means a 22003 is always telling you something real.

What Triggers This Error#

A number does not fit the target type. Six paths lead here, and each one wants a different fix:

• Inserted value exceeds the integer type: a literal or parameter is larger than smallint, integer, or bigint allows.
• Arithmetic overflow: a multiplication, a SUM(), or a running total passed the result type.
• NUMERIC(precision, scale) overflow: the integer part has more digits than precision − scale permits.
• Cast of an out-of-range value: '9999999999'::int or 1e20::bigint.
• SERIAL or identity sequence hit the ceiling: an integer key ran out at about 2.1 billion.
• COPY or bulk load carries a bad value: one row aborts the batch.

Fix by Scenario#

Inserted value exceeds the integer type#

PostgreSQL's integer types are bounded: smallint to 32,767, integer to 2,147,483,647, bigint to roughly 9.2 quintillion.

INSERT INTO sensors (reading) VALUES (40000);  -- column is smallint
-- ERROR: smallint out of range

Check the type, then widen it if the value is legitimate:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'sensors' AND column_name = 'reading';

ALTER TABLE sensors ALTER COLUMN reading TYPE integer;

If the number is wrong (a unit error, a bad parse), fix the source. Widening the column to absorb bad data just moves the problem downstream.

Arithmetic overflow#

The stored columns fit, but an expression over them does not:

SELECT SUM(bytes_transferred) FROM traffic;  -- integer SUM overflows
UPDATE counters SET n = n * 1000000;          -- the product exceeds integer

Cast to a wider type before the operation. One thing that surprises people: SUM over integer returns bigint, but SUM over bigint can still overflow. When a total has no natural ceiling, go to numeric.

SELECT SUM(bytes_transferred::numeric) FROM traffic;
ALTER TABLE counters ALTER COLUMN n TYPE bigint;

NUMERIC(precision, scale) overflow#

NUMERIC(5,2) allows three digits before the decimal point, because 5 − 2 = 3. So 1000.00 overflows, small as it looks.

INSERT INTO invoices (total) VALUES (1000.00);  -- column is NUMERIC(5,2)
-- ERROR: numeric field overflow
-- DETAIL: A field with precision 5, scale 2 must round to an absolute value less than 10^3.

Read the DETAIL line. It hands you the exact bound the value broke, which makes this the easiest 22003 to diagnose. Then raise the precision to cover real amounts:

ALTER TABLE invoices ALTER COLUMN total TYPE NUMERIC(12,2);

Cast of an out-of-range value#

Explicit casts overflow when the source is too big for the target:

SELECT '9999999999'::integer;  -- ERROR: value "9999999999" is out of range for type integer
SELECT (1e20)::bigint;         -- ERROR: bigint out of range

Cast to a type that holds the value, or validate the input and reject it before the cast:

SELECT '9999999999'::bigint;   -- fits

SERIAL or identity sequence hit the ceiling#

The 2.1-billion wall. An integer or serial primary key exhausts its range, and since sequences never reuse the gaps from deleted rows, a high-churn table can get there with far fewer than 2.1 billion rows actually present.

SELECT last_value FROM my_table_id_seq;

Migrate the column to bigint. On a large table this rewrites every row, so schedule a window or use a low-lock migration tool rather than running a plain ALTER against live traffic.

ALTER TABLE events ALTER COLUMN id TYPE bigint;
ALTER SEQUENCE events_id_seq AS bigint;

COPY or bulk load carries a bad value#

A single oversized value fails the entire COPY:

COPY metrics FROM '/tmp/metrics.csv' CSV HEADER;
-- ERROR: value "5000000000" is out of range for type integer
-- CONTEXT: COPY metrics, line 8120, column count

Postgres gives you the line number. Look at it directly, then fix the data or widen the column:

sed -n '8120p' /tmp/metrics.csv

For loads that run regularly, stage into a numeric or bigint table, validate, then insert into production. One bad row should not be able to sink the whole batch.

Prevention#

• Pick integer types from the real domain. As of PostgreSQL 17, reach for bigint on any identity key that could pass a few hundred million rows over its life. GENERATED ALWAYS AS IDENTITY over bigint is the safe default for anything high-churn.
• Size NUMERIC(p,s) from the largest legal amount, not the common one. Money and metric columns overflow when p is set too low, and the DETAIL line will tell you by how much.
• Promote unbounded aggregates to numeric. A SUM over bigint can still overflow.
• Validate numeric ranges in the application and in schema review, before the write reaches PostgreSQL.
• For bulk loads, stage into a wider table, validate, then insert. Do not bet a whole COPY on the cleanest row in the file.