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
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, sonextvalproduces 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 anumeric(5,2)column. - Casting a large text or float value to
integerorsmallint.
How to fix it
-
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;
-
Cast to a wider type before the arithmetic, not after.
SELECT qty::bigint * unit_price_cents FROM order_lines;
-
Increase the precision of a
numeric(p, s)column.ALTER TABLE invoices ALTER COLUMN total TYPE numeric(12, 2);
-
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
22001value too long for type22P02invalid input syntax for type22012division by zero8115SQL Server: Arithmetic overflow error converting1264MySQL: Out of range value for columnORA-01438Oracle: value larger than specified precision
More data exception errors
22021invalid byte sequence for encoding22008date/time field value out of range22007invalid datetime format22023invalid parameter value
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.
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.