PostgreSQL 22000 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

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

PostgreSQL error code 22000 represents a data exception, which is the parent error class for a family of errors that occur when a data value is invalid or incompatible within its operational context. Rather than appearing on its own, 22000 typically surfaces through more specific child codes such as 22001 (string truncation), 22003 (numeric out of range), or 22P02 (invalid text representation). Understanding this error class is essential for anyone building robust data pipelines or handling user input in PostgreSQL-backed applications.

Top 3 Causes

1. Invalid Type Cast

Attempting to cast a string value into an incompatible type (e.g., casting 'abc' to INTEGER) is the most common trigger for data exceptions.

-- This will raise a data exception
SELECT CAST('abc' AS INTEGER);
-- ERROR:  invalid input syntax for type integer: "abc"

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

SELECT safe_to_int('123');  -- Returns: 123
SELECT safe_to_int('abc');  -- Returns: NULL

2. Numeric Value Out of Range

Inserting a number that exceeds the storage capacity of the target column type causes this error. For example, SMALLINT only supports values between -32768 and 32767.

-- Create a table with a small integer column
CREATE TABLE inventory (
    item_id SERIAL PRIMARY KEY,
    stock   SMALLINT
);

-- This will fail
INSERT INTO inventory (stock) VALUES (99999);
-- ERROR:  smallint out of range

-- Fix: Upcast the column type
ALTER TABLE inventory ALTER COLUMN stock TYPE INTEGER;

-- Or safely clamp the value before insert
INSERT INTO inventory (stock)
VALUES (LEAST(GREATEST(99999, -32768), 32767));

3. String Length Truncation

Inserting a string longer than a VARCHAR(n) or CHAR(n) column's defined limit will raise error 22001, a child of 22000.

-- Column defined with a 10-character limit
CREATE TABLE users (
    username VARCHAR(10)
);

-- This will fail
INSERT INTO users (username) VALUES ('this_username_is_too_long');
-- ERROR:  value too long for type character varying(10)

-- Fix 1: Widen the column
ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(50);

-- Fix 2: Truncate input before inserting
INSERT INTO users (username)
VALUES (LEFT('this_username_is_too_long', 10));

-- Detect oversized values before bulk load
SELECT username, LENGTH(username) AS len
FROM staging_users
WHERE LENGTH(username) > 10;

Quick Fix Solutions

Wrap risky casts in PL/pgSQL exception handlers that catch data_exception and return NULL or a default value.
Pre-validate data in staging tables using regex checks before moving records to production tables.
Widen column types proactively — prefer INTEGER over SMALLINT and TEXT over VARCHAR(n) unless storage constraints are strict.
Use NUMERIC(p, s) carefully — always ensure the precision and scale accommodate your real-world data range with some headroom.

-- Bulk-load pattern: validate in staging, then insert clean data only
INSERT INTO orders (order_id, amount)
SELECT
    order_id::INTEGER,
    amount::NUMERIC(12, 2)
FROM staging_orders
WHERE order_id  ~ '^\d+$'
  AND amount    ~ '^\d+(\.\d{1,2})?$';

Prevention Tips

1. Use a staging table with TEXT columns for all external data loads.
Load raw data as TEXT first, run validation queries, and only promote clean rows to typed production columns. This prevents hard failures during bulk imports.

2. Add exception handling to all stored procedures and functions.
Always include a WHEN data_exception THEN block in PL/pgSQL code to log errors gracefully instead of letting them propagate and roll back entire transactions unexpectedly.

-- Graceful exception handling pattern
DO $$
BEGIN
    INSERT INTO orders (order_id, amount)
    VALUES ('xyz'::INTEGER, 100.00);
EXCEPTION
    WHEN data_exception THEN
        RAISE WARNING 'Skipping invalid row: %', SQLERRM;
END;
$$;

Related Error Codes

Code	Name	Description
`22001`	`string_data_right_truncation`	String exceeds column length
`22003`	`numeric_value_out_of_range`	Number exceeds type bounds
`22007`	`invalid_datetime_format`	Malformed date/time string
`22012`	`division_by_zero`	Division by zero attempted
`22P02`	`invalid_text_representation`	Cannot parse text into target type

Since 22000 is the parent class, a single WHEN data_exception THEN handler in PL/pgSQL will catch all 22xxx family errors — a convenient pattern for building fault-tolerant data processing routines.

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