EC
22018
PostgreSQLERRORNotableData ExceptionHIGH confidence

invalid character value for cast

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
  1. 1Casting or converting a string to a numeric, boolean, or date type when the string content is not valid for that type
  2. 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
trigger — this will error
SELECT 'not-a-number'::INTEGER;

expected output

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

Fix 1

Validate and clean data before casting

WHEN When processing external data with unpredictable formats.

Validate and clean data before casting
SELECT CASE WHEN value ~ '^[0-9]+
#x27; 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 When a NULL-on-failure pattern is needed throughout the codebase.

Use a custom safe-cast function with exception handling
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 documentation ↗

Class 22 — Data Exception

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors