PostgreSQL Error 22003: Numeric Value Out of Range
PostgreSQL error code 22003 (numeric_value_out_of_range) is raised when you attempt to store or compute a value that exceeds the boundaries of a numeric data type. This can happen during a simple INSERT, an UPDATE, or even a complex arithmetic operation inside a query. It is one of the most common data integrity errors in production environments, especially during data migrations or high-volume batch processing.
Top 3 Causes
1. Inserting a Value Beyond the Column's Integer Range
Each PostgreSQL integer type has a hard limit: SMALLINT holds up to 32,767, INTEGER up to ~2.1 billion, and BIGINT up to ~9.2 quintillion.
-- Problematic: SMALLINT column can't hold 40000
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
quantity SMALLINT
);
INSERT INTO orders (quantity) VALUES (40000);
-- ERROR: smallint out of range
-- Fix: Alter the column to a wider type
ALTER TABLE orders
ALTER COLUMN quantity TYPE INTEGER;
INSERT INTO orders (quantity) VALUES (40000);
-- INSERT 0 1
2. NUMERIC Precision and Scale Overflow
A NUMERIC(p, s) column can only store values where the total significant digits fit within p. For example, NUMERIC(5, 2) maxes out at 999.99.
-- Problematic: NUMERIC(5,2) cannot store 1000.00
CREATE TABLE products (
id SERIAL PRIMARY KEY,
price NUMERIC(5, 2)
);
INSERT INTO products (price) VALUES (1000.00);
-- ERROR: numeric field overflow
-- DETAIL: A field with precision 5, scale 2 must round to an
-- absolute value less than 10^3.
-- Fix: Increase precision
ALTER TABLE products
ALTER COLUMN price TYPE NUMERIC(12, 2);
INSERT INTO products (price) VALUES (1000.00);
-- INSERT 0 1
3. Arithmetic Overflow During Computation
Multiplying two INTEGER columns can silently overflow if the result exceeds ~2.1 billion. This is especially dangerous inside SUM() aggregates on large datasets.
-- Problematic: INTEGER * INTEGER can overflow
SELECT unit_price * quantity AS total FROM sales;
-- ERROR: integer out of range
-- Fix: Cast operands to BIGINT before multiplying
SELECT unit_price::BIGINT * quantity::BIGINT AS total FROM sales;
-- Safe aggregate
SELECT SUM(unit_price::BIGINT * quantity::BIGINT) AS grand_total
FROM sales;
Quick Fix Solutions
-- 1. Check the actual max/min values before migrating
SELECT MAX(quantity), MIN(quantity) FROM orders;
-- 2. Detect out-of-range rows before altering a column
SELECT id, quantity
FROM orders
WHERE quantity > 32767 OR quantity < -32768;
-- 3. Use explicit casting in calculations
SELECT CAST(col_a AS BIGINT) + CAST(col_b AS BIGINT) FROM my_table;
-- 4. Check column type metadata
SELECT column_name, data_type, numeric_precision, numeric_scale
FROM information_schema.columns
WHERE table_name = 'products';
Prevention Tips
1. Design schemas with growth in mind.
Always choose a type one level larger than your current maximum. Use BIGINT for counters and IDs, and NUMERIC(15, 2) or wider for monetary values. Refactoring a column type in production is costly and risky.
2. Add CHECK constraints and monitor range utilization.
-- Guard against business rule violations
ALTER TABLE products
ADD CONSTRAINT chk_price_positive
CHECK (price > 0 AND price < 1000000000);
-- Periodically monitor how close you are to the type limit
SELECT MAX(quantity)::NUMERIC / 2147483647 * 100 AS pct_used
FROM orders;
If pct_used exceeds 70–80%, it is time to plan a type upgrade before it becomes an outage.
Related Errors
| Code | Name | Brief Description |
|---|---|---|
22001 | string_data_right_truncation | String value too long for the column |
22P02 | invalid_text_representation | Invalid cast from text to a numeric type |
22012 | division_by_zero | Arithmetic division by zero |
22023 | invalid_parameter_value | Function argument outside allowed range |
📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.
SOCIAL SHARE CARD GENERATOR