PostgreSQL 22018 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22018: Invalid Character Value for Cast

PostgreSQL error code 22018 (invalid_character_value_for_cast) occurs when you attempt to cast a string value to another data type, but the string contains characters or a format that the target type cannot accept. This commonly surfaces during data migrations, ETL pipelines, or when processing user-supplied input without proper sanitization.

Top 3 Causes

1. Casting Dirty Strings to Numeric Types

Strings containing commas, currency symbols, or spaces cannot be directly cast to INTEGER, NUMERIC, or FLOAT.

-- These will all fail
SELECT CAST('1,234' AS INTEGER);
-- ERROR: invalid input syntax for type integer: "1,234"

SELECT CAST('$99.99' AS NUMERIC);
-- ERROR: invalid input syntax for type numeric: "$99.99"

-- Safe fix: strip non-numeric characters first
SELECT CAST(REGEXP_REPLACE('$1,234.56', '[^0-9.]', '', 'g') AS NUMERIC);
-- Result: 1234.56

-- Or build a safe wrapper function
CREATE OR REPLACE FUNCTION safe_to_numeric(p_val TEXT)
RETURNS NUMERIC AS $$
BEGIN
    RETURN p_val::NUMERIC;
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT safe_to_numeric('abc');   -- Returns NULL instead of error
SELECT safe_to_numeric('99.5');  -- Returns 99.5

2. Invalid Date/Time Format Strings

Passing date strings with wrong formats or out-of-range values to DATE or TIMESTAMP types is a frequent offender.

-- These will fail
SELECT CAST('2023-13-01' AS DATE);
-- ERROR: date/time field value out of range: "2023-13-01"

SELECT '01/32/2023'::DATE;
-- ERROR: date/time field value out of range

-- Safe fix: use TO_DATE() with an explicit format mask
SELECT TO_DATE('20231201', 'YYYYMMDD');       -- Result: 2023-12-01
SELECT TO_DATE('01/12/2023', 'DD/MM/YYYY');   -- Result: 2023-12-01

-- Safe wrapper for batch processing
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 IMMUTABLE;

3. Casting to ENUM Types with Undefined or Wrong-Case Values

PostgreSQL ENUM types are case-sensitive. Casting 'ACTIVE' to an ENUM that only defines 'active' will fail.

CREATE TYPE user_status AS ENUM ('active', 'inactive', 'pending');

-- This fails
SELECT CAST('ACTIVE' AS user_status);
-- ERROR: invalid input value for enum user_status: "ACTIVE"

-- Fix 1: normalize to lowercase before casting
SELECT LOWER('ACTIVE')::user_status;  -- Result: active

-- Fix 2: use CASE for synonym mapping
SELECT
    CASE LOWER(raw_value)
        WHEN 'active'   THEN 'active'::user_status
        WHEN 'enabled'  THEN 'active'::user_status
        WHEN 'inactive' THEN 'inactive'::user_status
        ELSE 'pending'::user_status
    END
FROM (VALUES ('ACTIVE'), ('enabled'), ('unknown')) AS t(raw_value);

Quick Fix Summary

Scenario	Recommended Fix
Dirty numeric strings	`REGEXP_REPLACE` + safe wrapper function
Unknown date formats	`TO_DATE()` / `TO_TIMESTAMP()` with format mask
ENUM case mismatch	`LOWER()` before casting + synonym mapping
Mixed bad data in batch	Staging table with all `TEXT` columns

Prevention Tips

Use a staging table for all raw data ingestion. Declare every column as TEXT in a staging table, validate and transform values, then insert clean rows into the production table. This prevents a single bad row from aborting an entire batch load.

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

-- Load clean rows into production
INSERT INTO orders (order_id, amount, order_date)
SELECT
    order_id::INTEGER,
    safe_to_numeric(amount),
    safe_to_date(order_date)
FROM stg_raw
WHERE safe_to_numeric(amount) IS NOT NULL
  AND safe_to_date(order_date) IS NOT NULL;

Add CHECK constraints and use strict input validation at the application layer. Enforce format rules at the database level so corrupt data never reaches a cast operation in the first place. Combining database constraints with application-side validation creates a reliable two-layer defense against 22018 errors.

Related Error Codes

22007 – invalid_datetime_format: closely related; triggers on malformed datetime strings.
22003 – numeric_value_out_of_range: cast succeeds syntactically but the value exceeds the target type's range.
42846 – cannot_coerce: no cast path exists between the two types at all.

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