PostgreSQL 22003 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22003: Numeric Value Out of Range

PostgreSQL error code 22003 (numeric_value_out_of_range) is thrown when you attempt to store or compute a value that exceeds the boundary of a numeric data type. This can happen during a simple INSERT, an UPDATE, or even inside an aggregate function like SUM(). Understanding which data type is overflowing — and why — is the fastest path to a fix.

Top 3 Causes

1. Integer Column Overflow

The most common cause in production systems is an auto-increment primary key or counter column reaching the maximum value of its integer type. INTEGER maxes out at 2,147,483,647, and high-traffic tables can hit this ceiling faster than expected.

-- Detect sequences nearing their limit
SELECT
  sequencename,
  last_value,
  max_value,
  ROUND((last_value::NUMERIC / max_value::NUMERIC) * 100, 2) AS usage_pct
FROM pg_sequences
ORDER BY usage_pct DESC;

-- Reproduce the error
CREATE TABLE demo (id INTEGER);
INSERT INTO demo VALUES (2147483647);
INSERT INTO demo VALUES (2147483648); -- ERROR: 22003

2. NUMERIC Precision Overflow

When a NUMERIC(precision, scale) column receives a value whose digits exceed the defined precision, PostgreSQL immediately raises 22003. For example, NUMERIC(6, 2) can hold a maximum of 9999.99 — any larger value will fail.

-- Reproduce the error
CREATE TABLE products (price NUMERIC(6, 2));
INSERT INTO products VALUES (9999.99);   -- OK
INSERT INTO products VALUES (10000.00);  -- ERROR: 22003

-- Identify out-of-range rows before migration
SELECT * FROM staging_products
WHERE price > 9999.99;

3. Arithmetic / Aggregate Overflow

A SUM() or multiplication operation on an INTEGER column can produce a result that overflows the column's type, even if every individual row value is perfectly valid. This is a silent danger in large batch reporting queries.

-- Dangerous: SUM result may overflow INTEGER
SELECT SUM(quantity) FROM order_items;  -- risky on large tables

-- Safe: cast to BIGINT before aggregating
SELECT SUM(quantity::BIGINT) AS total_quantity
FROM order_items;

-- Safe multiplication
SELECT
  (unit_price::NUMERIC(15,4) * quantity::NUMERIC(15,4))::NUMERIC(20,4)
    AS line_total
FROM order_items;

Quick Fix Solutions

-- Fix 1: Widen an integer column
ALTER TABLE users ALTER COLUMN user_id TYPE BIGINT;

-- Fix 2: Expand NUMERIC precision
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(15, 2);

-- Fix 3: Use BIGINT / BIGSERIAL for new tables (best practice)
CREATE TABLE new_orders (
  order_id   BIGSERIAL PRIMARY KEY,
  amount     NUMERIC(15, 2),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Fix 4: Handle error gracefully in PL/pgSQL
DO $$
BEGIN
  INSERT INTO demo VALUES (2147483648);
EXCEPTION
  WHEN numeric_value_out_of_range THEN
    RAISE NOTICE 'Value out of range — skipped.';
END;
$$;

Prevention Tips

Always use BIGINT/BIGSERIAL for primary keys and counters. There is almost no downside to using BIGINT over INTEGER in modern hardware, and it eliminates the most common source of 22003 errors entirely.

Add CHECK constraints and monitor sequences regularly. Enforce value boundaries at the database level rather than relying solely on application logic. Schedule a daily job to alert you when any sequence exceeds 80% utilization — catching the problem before it becomes an outage.

-- Proactive CHECK constraint
ALTER TABLE products
  ADD CONSTRAINT chk_price_positive CHECK (price >= 0);

-- Daily sequence health check
SELECT sequencename, last_value, max_value,
  ROUND((last_value::NUMERIC / max_value::NUMERIC) * 100, 2) AS pct_used
FROM pg_sequences
WHERE (last_value::NUMERIC / max_value::NUMERIC) > 0.8;

📖 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.

DEV Community