DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

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 you try to convert a string value into a specific data type, but the string's format is incompatible with that type. This is one of the most common errors in production environments, typically surfacing during user input processing, ETL pipelines, or API integrations where data types aren't strictly validated before hitting the database.

Top 3 Causes & Fixes

1. Invalid Cast to Numeric or Date Types

Passing a non-numeric string to an integer or numeric column, or a malformed string to a date/timestamp column, is the most frequent cause.

-- ❌ This will fail
INSERT INTO users (age) VALUES ('twenty-five');
-- ERROR:  invalid input syntax for type integer: "twenty-five"

-- ✅ Validate before casting using regex
SELECT
    CASE
        WHEN raw_value ~ '^\d+$' THEN raw_value::integer
        ELSE NULL
    END AS safe_age
FROM staging_users;

-- ✅ Safe date parsing with validation
SELECT
    CASE
        WHEN raw_date ~ '^\d{4}-\d{2}-\d{2}$' THEN raw_date::date
        ELSE NULL
    END AS safe_date
FROM staging_events;
Enter fullscreen mode Exit fullscreen mode

2. Malformed UUID Values

The uuid type in PostgreSQL enforces a strict xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx format. Missing hyphens, wrong length, or invalid characters will all trigger 22P02.

-- ❌ This will fail (missing hyphens)
SELECT 'a0eebc999c0b4ef8bb6d61bd1a200001'::uuid;
-- ERROR:  invalid input syntax for type uuid

-- ✅ Create a reusable validation function
CREATE OR REPLACE FUNCTION is_valid_uuid(input TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    PERFORM input::uuid;
    RETURN TRUE;
EXCEPTION WHEN invalid_text_representation THEN
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- ✅ Use the function to safely filter records
SELECT *
FROM incoming_orders
WHERE is_valid_uuid(order_uuid_text);

-- ✅ Fix a 32-char hex string by inserting hyphens
SELECT (
    LEFT(raw_uuid, 8) || '-' ||
    SUBSTRING(raw_uuid, 9, 4) || '-' ||
    SUBSTRING(raw_uuid, 13, 4) || '-' ||
    SUBSTRING(raw_uuid, 17, 4) || '-' ||
    RIGHT(raw_uuid, 12)
)::uuid
FROM (SELECT 'a0eebc999c0b4ef8bb6d61bd1a200001' AS raw_uuid) t;
Enter fullscreen mode Exit fullscreen mode

3. Undefined ENUM Values

PostgreSQL ENUM types are case-sensitive and only accept pre-defined labels. Inserting 'Active' when the ENUM defines 'active' will throw this error.

-- ❌ Case mismatch causes the error
UPDATE users SET status = 'Active'::user_status;
-- ERROR:  invalid input syntax for type user_status: "Active"

-- ✅ Normalize to lowercase before casting
UPDATE users SET status = LOWER('Active')::user_status;

-- ✅ Add a missing value to the ENUM
ALTER TYPE user_status ADD VALUE IF NOT EXISTS 'suspended';

-- ✅ Check valid ENUM labels before bulk update
SELECT enumlabel
FROM pg_enum
JOIN pg_type ON pg_enum.enumtypid = pg_type.oid
WHERE pg_type.typname = 'user_status';
Enter fullscreen mode Exit fullscreen mode

Quick Prevention Tips

Use a staging table pattern for ETL. Load all raw data as TEXT first, validate format with regex, then insert into the production table. Isolate bad rows into an error table instead of failing the entire batch.

-- Load raw data as TEXT
CREATE TABLE stg_raw_users (raw_id TEXT, raw_age TEXT, raw_dob TEXT);

-- Insert only valid rows into production
INSERT INTO users (id, age, date_of_birth)
SELECT raw_id::integer, raw_age::integer, raw_dob::date
FROM stg_raw_users
WHERE raw_id  ~ '^\d+$'
  AND raw_age ~ '^\d+$'
  AND raw_dob ~ '^\d{4}-\d{2}-\d{2}$';
Enter fullscreen mode Exit fullscreen mode

Use DOMAIN types and CHECK constraints to enforce format rules at the database level, catching bad data before it ever reaches a column.

CREATE DOMAIN positive_integer AS INTEGER CHECK (VALUE > 0);
CREATE DOMAIN valid_email AS TEXT
    CHECK (VALUE ~ '^[^@]+@[^@]+\.[^@]{2,}$');

CREATE TABLE contacts (
    id    positive_integer PRIMARY KEY,
    email valid_email NOT NULL
);
Enter fullscreen mode Exit fullscreen mode

Related Errors

Code Name Note
22003 numeric_value_out_of_range Format is valid, but value exceeds type range
22007 invalid_datetime_format Datetime-specific format error
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.

Top comments (0)