How to Fix PostgreSQL Error: Numeric Field Overflow

ERROR: numeric field overflow (SQLSTATE 22003, condition numeric_value_out_of_range) is raised when PostgreSQL is asked to store or compute a value that does not fit the target numeric type. For NUMERIC(p, s) columns the message includes A field with precision <p>, scale <s> must round to an absolute value less than 10^<p-s>. The same SQLSTATE covers integer-type overflows (smallint, integer, bigint) and out of range cast failures. The statement aborts; no row is written.

What This Error Means

PostgreSQL enforces numeric type bounds at write time and at evaluation time. For NUMERIC(p, s), the column accepts values whose unsigned digit count to the left of the decimal point is at most p - s. The decimal-point side is enforced; the fractional side is rounded to s digits using banker's rounding. For integer types, the bounds are fixed (smallint: -32768 to 32767; integer: roughly ±2.1 billion; bigint: roughly ±9.2 × 10^18). Operations that produce intermediate values outside these ranges raise 22003 even if the final destination column could fit the result.

The error is data-driven: the same query with different parameter values may pass or fail. Common cases are computed values that exceed the destination precision, casts from wider to narrower types, and aggregations that sum past the integer bound.

Common Causes

NUMERIC(p, s) left-of-decimal capacity exceeded - check \d <table> for the declared precision/scale.
Multiplication or aggregation produces an intermediate that overflows the smaller operand's type - check with EXPLAIN VERBOSE.
Explicit cast narrows a value (::smallint on a value > 32767).
SUM(integer_column) over many rows exceeds the bigint boundary or, in some older drivers, returns bigint instead of numeric and overflows.
Sequence has caught up to bigint's upper bound - check pg_sequences.last_value against the max.
Inserting a fractional value into an integer column - PostgreSQL truncates, but if the integer part is out of range you get 22003.
ORM mapping mismatch (e.g. Java int mapped to PostgreSQL bigint) - the driver narrows before insert.

How to Fix `numeric_value_out_of_range`

Inspect the column's declared type. The fix depends on whether the type is NUMERIC(p, s) or an integer:

SELECT column_name, data_type, numeric_precision, numeric_scale
FROM information_schema.columns
WHERE table_name = 'invoices' AND column_name = 'amount';

Widen the column. For NUMERIC, increase the left-of-decimal capacity by raising precision (keeping scale fixed) or by switching to unbounded NUMERIC:

ALTER TABLE invoices
    ALTER COLUMN amount TYPE numeric(20, 2);

-- Unbounded: up to 131072 digits left of the decimal point
ALTER TABLE calculations
    ALTER COLUMN result TYPE numeric;

For integers, step up to the next size:

ALTER TABLE events ALTER COLUMN seq TYPE bigint;

Use bigint for sums of integer columns. Cast at the aggregation to avoid mid-computation overflow:
```
SELECT SUM(quantity::bigint) FROM line_items;
```
Bound input at the application boundary. Where the column is small on purpose (e.g. percentages), reject inputs outside the range before sending the SQL. A CHECK constraint catches the same case in the database:
```
ALTER TABLE discounts
    ADD CONSTRAINT discount_pct_range CHECK (pct BETWEEN 0 AND 100);
```
Migrate a near-exhausted serial to bigserial. A 4-byte serial (max ~2.1 billion) is a common cause of 22003 on id columns. Migrate to bigint:
```
ALTER TABLE big_table
    ALTER COLUMN id TYPE bigint;
ALTER SEQUENCE big_table_id_seq AS bigint;
```
Round explicitly to fit the scale. Storing more decimal places than s is silently rounded, but a guarded round avoids surprises:
```
INSERT INTO invoices(amount) VALUES (round($1::numeric, 2));
```

Audit the data before changing the type. Find rows that already violate the new bound or the existing one:

SELECT id, amount FROM invoices
WHERE abs(amount) >= 10^(numeric_precision - numeric_scale)
FROM (
    SELECT *, (SELECT numeric_precision FROM information_schema.columns
               WHERE table_name='invoices' AND column_name='amount') AS numeric_precision,
             (SELECT numeric_scale FROM information_schema.columns
               WHERE table_name='invoices' AND column_name='amount') AS numeric_scale
    FROM invoices
) sub;

Catch Numeric Field Overflow Before It Hits Production

Pulse provides AI-powered observability for PostgreSQL alongside Elasticsearch, ClickHouse, and Kafka. For SQLSTATE 22003 numeric_value_out_of_range errors, Pulse:

Tracks sequence consumption (pg_sequences.last_value / max_value) on every serial, bigserial, and identity column, alerting on usage percentage long before the first overflow lands on an id column
Correlates 22003 errors with the failing table and column from pg_stat_statements, so an under-declared numeric(10, 2) currency column or a sum(integer) aggregation overflow maps to a specific call site
Flags configuration drift across replicas and environments - mismatched column widths after a partial ALTER TABLE, sequence ranges that diverged from primary to standby after a logical replication mishap - before they show up as overflow errors in production

A serial column at 90% sequence usage is a ticking clock - Pulse turns that into a calendar item with weeks of notice instead of an outage page.

Connect your Postgres cluster to surface issues like this proactively.

Preventive Measures

Default to bigint for surrogate keys. The storage overhead over integer is 4 bytes per row and removes the exhaustion risk.
Use NUMERIC (without parameters) for results of arbitrary calculations; declare precision/scale only on columns whose range is genuinely bounded.
Alert on sequence usage percentage. A query like SELECT seqname, last_value::float/max_value FROM pg_sequences; is cheap.
Add CHECK constraints to columns with a known business range. A percent column should have CHECK (pct BETWEEN 0 AND 100).
For financial values, store the smallest unit (cents, millicents) as bigint and format in the application.

Frequently Asked Questions

Q: What is SQLSTATE 22003 in PostgreSQL?
A: 22003 is the numeric_value_out_of_range SQLSTATE. PostgreSQL raises it whenever a numeric value cannot be represented in the target type - too many left-of-decimal digits for a NUMERIC(p, s), or outside the fixed range of smallint, integer, or bigint.

Q: What is the maximum value PostgreSQL NUMERIC can store?
A: An unparameterized NUMERIC (or DECIMAL) supports up to 131072 digits before the decimal point and up to 16383 after. Parameterized NUMERIC(p, s) is bounded by your declared precision. There is no fixed numeric maximum analogous to bigint's 9.2 × 10^18.

Q: What is the difference between NUMERIC and DECIMAL in PostgreSQL?
A: They are aliases for the same type. The SQL standard distinguishes them in theory; PostgreSQL treats them identically.

Q: How do I prevent a SERIAL column from running out of values?
A: Use bigserial (or GENERATED ... AS IDENTITY over bigint) for any table that may exceed 2.1 billion rows. To migrate an existing serial, run ALTER TABLE ... ALTER COLUMN id TYPE bigint and ALTER SEQUENCE ... AS bigint. Both operations rewrite minimal data when applied separately.

Q: Why does SUM of an integer column overflow?
A: PostgreSQL's sum(integer) returns bigint and rarely overflows on its own. The common path to 22003 is sum(int_col * int_col) where the intermediate multiplication overflows integer before the sum widens. Cast one factor to bigint or numeric.

Q: Does NUMERIC have a performance cost compared to integer types?
A: Yes. NUMERIC is variable-length and uses software arithmetic in the executor, while integer types use hardware instructions. For high-volume aggregations, bigint is several times faster. Use NUMERIC only when you need its exact-precision semantics, typically for monetary values.

Q: How can I detect PostgreSQL numeric field overflow before it breaks the application?
A: Monitor pg_sequences.last_value / max_value on every serial/identity column and alert above 80% usage. Pulse tracks sequence consumption across all sequences, correlates 22003 errors with the failing column from pg_stat_statements, and flags under-declared numeric(p, s) columns whose real-world range is creeping toward the precision ceiling.

PostgreSQL Invalid Input Syntax: the matching 22P02 error for unparseable text.
PostgreSQL Check Constraint Violated: enforce numeric ranges declaratively.
PostgreSQL CREATE TABLE: declaring NUMERIC(p, s) and integer types.
PostgreSQL Cast Operator: explicit casts that can trigger 22003.
Common PostgreSQL Errors: index of SQLSTATE codes and conditions.
PostgreSQL AVG Function: aggregation return-type behavior.