// PostgreSQL error reference

PostgreSQL 22003: numeric value out of range

A number doesn't fit its type: an integer overflow, a value too big for numeric(p,s), or an id column that hit the int4 limit.

SQLSTATE
22003
Name
numeric_value_out_of_range
Category
Data Exception
Error message
integer out of range

What it means

Each numeric type has a range: smallint up to 32767, integer up to 2147483647, bigint up to about 9.2 × 10^18, and numeric(p, s) up to p digits with s after the point. A value or a calculation result outside the range fails with 22003. The message names the type: integer out of range, bigint out of range, smallint out of range, or numeric field overflow for numeric(p, s).

Arithmetic on two integers is done in integer, so count * price_in_cents can overflow even when the final answer is stored in a bigint column.

Common causes

  • A serial (integer) primary key reached 2147483647, so nextval produces a value the column can't hold.
  • Multiplying or summing integers whose result exceeds the integer range before it is cast.
  • A value with more digits than numeric(p, s) allows, for example 1000.00 in a numeric(5,2) column.
  • Casting a large text or float value to integer or smallint.

How to fix it

  1. Widen the column; for an integer id that is running out, move to bigint (this rewrites the table, so plan it).

    ALTER TABLE events ALTER COLUMN id TYPE bigint;
    ALTER SEQUENCE events_id_seq AS bigint;
  2. Cast to a wider type before the arithmetic, not after.

    SELECT qty::bigint * unit_price_cents FROM order_lines;
  3. Increase the precision of a numeric(p, s) column.

    ALTER TABLE invoices ALTER COLUMN total TYPE numeric(12, 2);
  4. Check how close a sequence is to its limit.

    SELECT last_value, max_value FROM pg_sequences WHERE sequencename = 'events_id_seq';

Reproduce it safely

SELECT 2147483647 + 1;      -- ERROR: integer out of range
SELECT 1000::numeric(5, 2);  -- ERROR: numeric field overflow

Related errors

More data exception errors

The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.

// fewer errors, faster fixes

Work on PostgreSQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.