PostgreSQL: numeric field overflow

What it means

numeric(p, s) (also written decimal(p, s)) stores numbers with at most p digits in total, s of them after the decimal point. That leaves p − s digits before the point: numeric(5,2) holds up to 999.99, and numeric(3,3) holds values below 1. Your value needed more digits before the point, so it was rejected:

ERROR:  numeric field overflow
DETAIL:  A field with precision 5, scale 2 must round to an absolute value less than 10^3.

The DETAIL is the useful part: precision 5, scale 2, so the value must be below 10³ = 1000.

Extra digits after the point are not an error; they’re rounded to the scale. Rounding happens first, so 999.994 is stored as 999.99, while 999.995 rounds to 1000.00 and overflows.

Like value too long, the message doesn’t name the column. Match the precision and scale in DETAIL against your table’s numeric columns.

Common causes

The column was sized for smaller numbers: prices, totals or quantities that outgrew numeric(8,2), a balance in a currency with bigger amounts.
A rate or percentage stored as a fraction (numeric(3,3), below 1) receiving 1, or 20 instead of 0.20.
Units mixed up: cents into a column meant for whole units, or milliseconds into one meant for seconds.
A computed value cast to a narrow type, such as (amount * 100)::numeric(5,2).

How to fix it

Find the column

SELECT column_name, numeric_precision, numeric_scale
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'prices'
  AND data_type = 'numeric';

Look for the one whose precision and scale match the DETAIL.

Widen the column

Raising the precision while keeping the scale doesn’t rewrite the table:

ALTER TABLE prices ALTER COLUMN amount TYPE numeric(12,2);

Removing the limit (TYPE numeric) doesn’t rewrite it either. Changing the scale (numeric(12,4)) does rewrite the table. All of these take a brief ACCESS EXCLUSIVE lock; see changing a column’s type.

Plain numeric without (p, s) accepts any size and keeps every digit you give it. Keep the limits where they’re a real rule (two decimal places for money), and make the precision generous.

Fix the value

If the limit is right, the input isn’t: convert a percentage to a fraction (20 to 0.20), cents to units, or reject the input earlier with a clear message. For data already loaded into a staging table, find the offenders:

SELECT id, amount FROM staging WHERE abs(round(amount, 2)) >= 1000;

Reproduce it

On PostgreSQL 18.6:

CREATE TABLE prices (id integer PRIMARY KEY, amount numeric(5,2), rate numeric(3,3));

INSERT INTO prices (id, amount) VALUES (1, 999.99);   -- fits
INSERT INTO prices (id, amount) VALUES (2, 1000);
INSERT INTO prices (id, rate) VALUES (5, 1);

ERROR:  numeric field overflow
DETAIL:  A field with precision 5, scale 2 must round to an absolute value less than 10^3.
ERROR:  numeric field overflow
DETAIL:  A field with precision 3, scale 3 must round to an absolute value less than 1.

999.994 was stored as 999.99; 999.999 and 999.995 failed like 1000. SELECT 12345.678::numeric(6,2) failed with … must round to an absolute value less than 10^4., and (amount * 100)::numeric(5,2) on the stored 999.99 failed the same way as the insert.

Changing amount from numeric(5,2) to numeric(12,2), then to plain numeric, kept the table’s relfilenode (no rewrite); changing it to numeric(12,4) gave it a new one (a rewrite). With \set VERBOSITY verbose, psql shows the code: ERROR: 22003: numeric field overflow. PostgreSQL 14.24 gives the same messages.

In Inlet

The structure editor changes a column’s type, shows the DDL, and warns when the change rewrites or scans the table. When a statement fails, Inlet shows the server’s error; with your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix it, sending the schema, the SQL and the error, never rows.

numeric field overflow