PostgreSQL 22003 Error: Causes and Solutions Complete Guide

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

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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';

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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.