PostgreSQL 22000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22000: Data Exception — What It Means and How to Fix It

PostgreSQL error code 22000 (data_exception) is the parent class for a family of errors that occur when a data value is invalid, out of range, or in the wrong format for its intended type. Rather than appearing on its own, it typically surfaces through more specific child codes such as 22001 (string too long), 22003 (numeric overflow), or 22P02 (invalid text representation). Understanding this error class is essential for anyone building data pipelines, running migrations, or handling user-generated input.

---

Top 3 Causes

1. Invalid Type Cast

Trying to cast a string that doesn't match the target type is the most common trigger.

-- This will raise ERROR 22P02 (child of 22000)
SELECT 'not_a_number'::INTEGER;

-- Safe alternative using a wrapper function
CREATE OR REPLACE FUNCTION safe_to_int(p_val TEXT)
RETURNS INTEGER LANGUAGE plpgsql AS $$
BEGIN
    RETURN p_val::INTEGER;
EXCEPTION
    WHEN data_exception THEN RETURN NULL;
END;
$$;

SELECT safe_to_int('42');        -- Returns: 42
SELECT safe_to_int('abc');       -- Returns: NULL (no crash)

-- Validate before bulk insert
SELECT raw_value
FROM staging_data
WHERE raw_value !~ '^-?[0-9]+$'; -- find non-integer rows first

2. Column Length or Precision Overflow

Inserting a value that exceeds a column's defined size or numeric precision raises this error class.

-- Setup
CREATE TABLE products (
    sku     VARCHAR(8)     NOT NULL,
    price   NUMERIC(6, 2)  NOT NULL
);

-- ERROR: value too long for type character varying(8)
INSERT INTO products (sku, price) VALUES ('TOOLONGSKU123', 19.99);

-- Fix 1: Truncate at insert time
INSERT INTO products (sku, price)
VALUES (LEFT('TOOLONGSKU123', 8), 19.99);

-- Fix 2: Widen the column definition
ALTER TABLE products ALTER COLUMN sku TYPE VARCHAR(20);

-- Fix 3: Pre-flight check before bulk load
SELECT COUNT(*) AS bad_rows
FROM staging_products
WHERE LENGTH(sku) > 8 OR price > 9999.99;

3. Invalid or Out-of-Range Date/Time Values

PostgreSQL strictly validates date and time values — logically impossible dates cause immediate failures.

-- ERROR: date/time field value out of range
SELECT '2024-02-30'::DATE;
SELECT '2024-13-01'::DATE;

-- Safe date validation function
CREATE OR REPLACE FUNCTION is_valid_date(p_val TEXT)
RETURNS BOOLEAN LANGUAGE plpgsql AS $$
BEGIN
    PERFORM p_val::DATE;
    RETURN TRUE;
EXCEPTION
    WHEN data_exception THEN RETURN FALSE;
END;
$$;

-- Filter only valid rows before insert
INSERT INTO events (event_name, event_date)
SELECT name, raw_date::DATE
FROM staging_events
WHERE is_valid_date(raw_date);

-- Quick audit of bad dates in staging
SELECT raw_date, COUNT(*) AS occurrences
FROM staging_events
WHERE NOT is_valid_date(raw_date)
GROUP BY raw_date
ORDER BY occurrences DESC;

---

Quick Fix Solutions

• Catch it in PL/pgSQL: Use WHEN data_exception THEN to handle the entire 22000 class at once.
• Pre-validate with regex: Check string format before casting (~ '^[0-9]+$').
• Use staging tables: Load raw data without constraints, validate, then promote to production tables.
• Add CHECK constraints: Let the database enforce business rules at the storage layer.

-- Catching the entire 22000 class in a block
DO $$
BEGIN
    PERFORM '9999-99-99'::DATE;
EXCEPTION
    WHEN data_exception THEN
        RAISE NOTICE 'Caught a data exception — value skipped.';
END;
$$;

---

Prevention Tips

1. Define CHECK constraints and DOMAINs early.

CREATE DOMAIN positive_price AS NUMERIC(10, 2) CHECK (VALUE > 0);

CREATE TABLE items (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,
    price positive_price NOT NULL
);

2. Run a validation query before every bulk load.

-- Example pre-load validation report
SELECT
    SUM(CASE WHEN LENGTH(username) > 50 THEN 1 ELSE 0 END)    AS username_too_long,
    SUM(CASE WHEN NOT is_valid_date(signup_date) THEN 1 ELSE 0 END) AS bad_dates,
    SUM(CASE WHEN score !~ '^[0-9.]+$' THEN 1 ELSE 0 END)     AS non_numeric_score
FROM staging_users;

---

Related Error Codes

Code	Name	When It Happens
22001	string_data_right_truncation	String exceeds column length
22003	numeric_value_out_of_range	Number too large for column type
22007	invalid_datetime_format	Unrecognized date/time format string
22012	division_by_zero	Dividing by zero in an expression
22P02	invalid_text_representation	Text cannot be parsed into target type

Mastering error class 22000 means treating data validation as a database responsibility, not just an application concern. Build your constraints, test your casts, and validate before you load — your future self will thank you.

---

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