DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

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) is thrown when you attempt to cast a string value into a target data type, but the string's content is incompatible with that type. This commonly happens during data migrations, ETL pipelines, or when processing raw user input that hasn't been validated before being passed to the database.

Top 3 Causes

1. Casting Non-Numeric Strings to Numeric Types

Strings containing commas, letters, or multiple decimals cannot be cast directly to INTEGER, NUMERIC, or FLOAT.

-- Triggers ERROR 22018
SELECT CAST('1,234' AS INTEGER);
SELECT CAST('abc'   AS NUMERIC);
SELECT CAST('12.34.56' AS FLOAT);

-- Safe fix: strip non-numeric characters first
SELECT CAST(REPLACE('1,234', ',', '') AS INTEGER); -- returns 1234

-- Or validate with regex before casting
SELECT
    CASE
        WHEN col ~ '^-?[0-9]+(\.[0-9]+)?$' THEN col::NUMERIC
        ELSE NULL
    END AS safe_num
FROM raw_data;
Enter fullscreen mode Exit fullscreen mode

2. Casting Invalid Strings to Date/Timestamp Types

Strings that don't represent a real calendar date will fail when cast to DATE or TIMESTAMP.

-- Triggers ERROR 22018
SELECT CAST('2024/13/45'  AS DATE);
SELECT CAST('not-a-date'  AS TIMESTAMP);

-- Safe fix: use TO_DATE with explicit format
SELECT TO_DATE('31/12/2024', 'DD/MM/YYYY'); -- returns 2024-12-31

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

SELECT safe_to_date('2024-12-31');   -- 2024-12-31
SELECT safe_to_date('not-a-date');   -- NULL
Enter fullscreen mode Exit fullscreen mode

3. Casting Unsupported Strings to Boolean or Strict Types

PostgreSQL's BOOLEAN type only accepts a limited set of string values. Custom strings like 'Y', 'N', or 'TRUE_VALUE' will cause this error. The same applies to strict types like UUID and INET.

-- Triggers ERROR 22018
SELECT CAST('Y'           AS BOOLEAN);
SELECT CAST('not-a-uuid'  AS UUID);

-- Safe fix for boolean: use CASE WHEN
SELECT
    CASE
        WHEN upper(flag) IN ('Y','YES','1','TRUE','T')  THEN TRUE
        WHEN upper(flag) IN ('N','NO','0','FALSE','F')  THEN FALSE
        ELSE NULL
    END AS bool_flag
FROM source_table;

-- Safe UUID cast function
CREATE OR REPLACE FUNCTION safe_to_uuid(p_val TEXT)
RETURNS UUID LANGUAGE plpgsql AS $$
BEGIN
    RETURN p_val::UUID;
EXCEPTION WHEN OTHERS THEN RETURN NULL;
END; $$;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Build a shared library of safe casting functions to use across all queries and ETL scripts:

-- One-stop safe cast toolkit
CREATE SCHEMA IF NOT EXISTS util;

CREATE OR REPLACE FUNCTION util.to_int(v TEXT) RETURNS INTEGER
LANGUAGE plpgsql AS $$ BEGIN RETURN v::INTEGER;
EXCEPTION WHEN OTHERS THEN RETURN NULL; END; $$;

CREATE OR REPLACE FUNCTION util.to_numeric(v TEXT) RETURNS NUMERIC
LANGUAGE plpgsql AS $$ BEGIN RETURN v::NUMERIC;
EXCEPTION WHEN OTHERS THEN RETURN NULL; END; $$;

CREATE OR REPLACE FUNCTION util.to_date(v TEXT) RETURNS DATE
LANGUAGE plpgsql AS $$ BEGIN RETURN v::DATE;
EXCEPTION WHEN OTHERS THEN RETURN NULL; END; $$;

-- Usage in a real ETL scenario
SELECT
    util.to_int(raw_id)       AS id,
    util.to_numeric(raw_price) AS price,
    util.to_date(raw_date)    AS event_date
FROM staging_import;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Validate data before it enters the database. Add CHECK constraints on staging tables to enforce format rules, and validate values at the application layer before issuing any SQL. 
ALTER TABLE staging_orders
ADD CONSTRAINT chk_price_format
CHECK (raw_price ~ '^[0-9]+(\.[0-9]{1,2})?$');
Enter fullscreen mode Exit fullscreen mode
  1. Never use raw CAST() or :: in ETL code. Standardize on your util.safe_cast_* function library so that all type conversions are failure-tolerant and centrally maintained. This makes it easy to log bad values, return NULL gracefully, and fix behavior in one place.

Related Errors

Code Name Notes
22007 invalid_datetime_format Wrong date format string
22003 numeric_value_out_of_range Value too large for target type
22P02 invalid_text_representation Common with UUID, INET casts
42846 cannot_coerce No cast path defined between types

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