invalid character value for cast

Category: Data ExceptionVersions: All Postgres versions

What this means

SQLSTATE 22018 is raised when a character string cannot be cast to the target type because it contains a value that is not valid for that type. For example, casting the string "abc" to INTEGER.

Why it happens

1Casting or converting a string to a numeric, boolean, or date type when the string content is not valid for that type
2Data imported from CSV or text sources that has mixed or unexpected formats in typed columns

How to reproduce

Casting a non-numeric string to integer.

trigger — this will ERROR

SELECT 'not-a-number'::INTEGER;

ERROR: invalid input syntax for type integer: "not-a-number"

Fix 1: Validate and clean data before casting

When processing external data with unpredictable formats.

fix

SELECT CASE WHEN value ~ '^[0-9]+
    
    
    
  

 THEN value::INTEGER ELSE NULL END
FROM import_staging;

Why this works

The regex check confirms the value is all digits before the cast, returning NULL for non-numeric strings instead of an error.

Fix 2: Use a custom safe-cast function with exception handling

When a NULL-on-failure pattern is needed throughout the codebase.

fix

CREATE OR REPLACE FUNCTION safe_int(p_val TEXT) RETURNS INTEGER AS $
BEGIN
  RETURN p_val::INTEGER;
EXCEPTION WHEN invalid_text_representation THEN
  RETURN NULL;
END;
$ LANGUAGE plpgsql IMMUTABLE;

Why this works

The function absorbs cast failures and returns NULL, allowing bulk processing to continue past bad values.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

🔧 Source ref: Class 22 — Data Exception

Confidence assessment

✅ HIGH confidence

Standard SQLSTATE for cast failures. Behaviour consistent across all Postgres versions.