PostgreSQL 22003 Error: Causes and Solutions Complete Guide

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

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

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

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

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