PostgreSQL 22P02 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22P02: invalid text representation

PostgreSQL error 22P02 (invalid_text_representation) occurs when a string value cannot be converted into the target data type because it doesn't match the expected format. This commonly happens when inserting unvalidated user input or raw external data directly into typed columns such as INTEGER, UUID, DATE, or custom ENUM types.

Top 3 Causes

1. Inserting Non-Numeric Strings into Numeric Columns

This is the most frequent cause. Values like 'N/A', 'unknown', or empty strings often come from CSV files or APIs and break numeric type casting.

-- Triggers 22P02
SELECT CAST('N/A' AS INTEGER);
-- ERROR: invalid input syntax for type integer: "N/A"

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

SELECT safe_to_int('42');    -- Returns: 42
SELECT safe_to_int('N/A');   -- Returns: NULL

2. Malformed UUID or ENUM Values

PostgreSQL's UUID type requires a strict xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx format. Custom ENUM types also require exact case-sensitive matches.

-- Triggers 22P02
INSERT INTO sessions (id) VALUES ('not-a-uuid');
-- ERROR: invalid input syntax for type uuid: "not-a-uuid"

-- Safe fix: validate format before casting
SELECT CASE
    WHEN val ~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$'
    THEN val::UUID
    ELSE NULL
END AS safe_uuid
FROM raw_data;

-- For ENUM: check valid labels first
SELECT enumlabel FROM pg_enum
JOIN pg_type ON pg_enum.enumtypid = pg_type.oid
WHERE pg_type.typname = 'your_enum_name';

3. Invalid Date/Time Format Strings

PostgreSQL is strict about date formats. Strings like '20240113' or '01/13/2024' without explicit format guidance will fail.

-- Triggers 22P02
SELECT '20240113'::DATE;
-- ERROR: invalid input syntax for type date: "20240113"

-- Safe fix: use TO_DATE with explicit format
SELECT TO_DATE('20240113', 'YYYYMMDD');          -- 2024-01-13
SELECT TO_DATE('01/13/2024', 'MM/DD/YYYY');      -- 2024-01-13

-- Safe wrapper function
CREATE OR REPLACE FUNCTION safe_to_date(p_val TEXT, p_fmt TEXT DEFAULT 'YYYY-MM-DD')
RETURNS DATE AS $$
BEGIN
    RETURN TO_DATE(p_val, p_fmt);
EXCEPTION
    WHEN OTHERS THEN RETURN NULL;
END;
$$ LANGUAGE plpgsql;

SELECT safe_to_date('2024-01-13');     -- 2024-01-13
SELECT safe_to_date('not-a-date');     -- NULL

Quick Fix Solutions

Always use TO_DATE(), TO_TIMESTAMP(), and TO_NUMBER() with explicit format strings instead of raw casting.
Wrap risky casts in exception-handling PL/pgSQL functions that return NULL on failure.
Use regex validation (~ operator) before casting: WHERE col ~ '^\d+$' for integers.

Prevention Tips

Use a staging table pattern for external data. Load all incoming data as TEXT first, validate and transform it, then insert into the final typed table. This isolates 22P02 errors to the transformation step where they are easiest to handle.

-- Stage everything as TEXT
CREATE TABLE staging_raw (order_id TEXT, amount TEXT, order_date TEXT);

-- Validate and load into production table
INSERT INTO orders (order_id, amount, order_date)
SELECT
    safe_to_int(order_id),
    safe_to_numeric(amount),
    safe_to_date(order_date)
FROM staging_raw
WHERE order_id ~ '^\d+$'
  AND amount ~ '^\d+(\.\d{1,2})?$'
  AND safe_to_date(order_date) IS NOT NULL;

Define DOMAIN types with CHECK constraints to enforce format rules at the schema level, catching bad data before it reaches your application logic.

CREATE DOMAIN positive_int AS INTEGER CHECK (VALUE > 0);
CREATE DOMAIN us_phone AS TEXT CHECK (VALUE ~ '^\d{3}-\d{3}-\d{4}$');

Related Errors

Code	Name	When it appears
22007	invalid_datetime_format	Wrong format string in `TO_DATE`/`TO_TIMESTAMP`
22003	numeric_value_out_of_range	Valid number format but exceeds type range
23502	not_null_violation	NULL result from 22P02 handling hits a NOT NULL column
42804	datatype_mismatch	Wrong type passed to a function or operator

📖 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