PostgreSQL Invalid Character Value for Cast (SQLSTATE 22018)

PostgreSQL raises ERROR: invalid input syntax for type <type>: "<value>" with SQLSTATE 22018 (invalid_character_value_for_cast) when a character string value cannot be converted to the target data type because the string's content is not a valid representation of that type. You will typically see messages such as ERROR: invalid input syntax for type integer: "abc" or ERROR: invalid input syntax for type numeric: "12.3.4".

What This Error Means

SQLSTATE 22018 belongs to error class 22 — Data Exception — which covers a wide range of errors arising from invalid data values during SQL operations. invalid_character_value_for_cast is specifically triggered by an explicit or implicit cast operation where PostgreSQL's input conversion routine rejects the string.

When PostgreSQL evaluates a cast such as 'abc'::integer or CAST('12.3.4' AS numeric), it passes the string to the target type's input function (e.g., int4in, numeric_in). If that function cannot parse the string as a valid representation of the type, it raises this error. The same mechanism fires for implicit casts that occur during comparisons, function calls, or INSERT/UPDATE operations when a text or varchar value is coerced into a non-character type.

When this error occurs inside a transaction, the transaction is automatically placed into an aborted state. Subsequent statements in the same transaction will fail with ERROR: current transaction is aborted, commands ignored until end of transaction block until you issue a ROLLBACK or use a savepoint to recover.

Common Causes

1. Casting a non-numeric string to a numeric type. Strings containing letters, extra decimal points, thousand-separator commas, currency symbols, or whitespace in an unexpected position fail when cast to integer, bigint, numeric, float, etc. For example, '1,234'::integer or '$99.99'::numeric both raise 22018.

2. Parsing data imported from CSV or external sources. ETL pipelines, COPY commands, and bulk inserts that bring in raw text data frequently encounter poorly formatted values: empty strings being cast to a numeric column, date strings in an unexpected locale format ('13/01/2024' where MM/DD/YYYY is expected), or boolean-like strings ('yes', '1') being cast to boolean incorrectly.

3. Implicit casts triggered by schema or ORM mismatch. An ORM or application layer may bind a parameter as text while the column or function expects integer or uuid. If the bound string is malformed (e.g., a partial UUID), PostgreSQL's implicit cast raises 22018.

4. Explicit CAST or :: operator on user-supplied input. Application code that constructs queries with inline string values and casts them — without prior validation — will raise this error for any unexpected input value.

5. Locale-sensitive numeric formats. On databases with a non-default lc_numeric locale, to_number() behaves differently, but direct casts (::numeric) always expect the standard PostgreSQL decimal format regardless of locale settings. Input using a locale-specific decimal comma (e.g., '3,14'::numeric) will fail.

How to Fix invalid_character_value_for_cast

1. Validate or sanitize input before casting. Use CASE expressions or NULLIF to handle problematic values upstream:

   -- Replace blank strings with NULL before casting
   SELECT NULLIF(trim(raw_value), '')::integer
   FROM raw_import;
   

2. Use regexp_replace or translate to strip formatting characters before casting numeric strings that may contain commas or currency symbols:

   SELECT translate(price_text, '$,', '')::numeric AS price
   FROM product_import;
   

3. Write a safe-cast helper function to return NULL instead of raising an error on unparseable input:

   CREATE OR REPLACE FUNCTION safe_cast_integer(p_text text)
   RETURNS integer
   LANGUAGE plpgsql IMMUTABLE
   AS $$
   BEGIN
     RETURN p_text::integer;
   EXCEPTION
     WHEN invalid_character_value_for_cast
       OR numeric_value_out_of_range THEN
       RETURN NULL;
   END;
   $$;
   
   SELECT safe_cast_integer('abc');   -- returns NULL
   SELECT safe_cast_integer('42');    -- returns 42
   

4. Catch the error in application code. All PostgreSQL drivers expose the SQLSTATE code. Check for 22018 and surface a user-friendly validation error rather than a raw database exception:

   import psycopg2
   
   try:
       cur.execute("SELECT %s::integer", (user_input,))
   except psycopg2.errors.InvalidCharacterValueForCast:
       # Handle gracefully — log, return 400, etc.
       raise ValueError(f"'{user_input}' is not a valid integer")
   

5. For COPY imports, use a staging table with text columns, validate there, then insert into the typed target table:

   -- Stage raw data as text
   CREATE TEMP TABLE import_stage (id text, amount text, created_at text);
   COPY import_stage FROM '/path/to/data.csv' CSV HEADER;
   
   -- Validate and insert, skipping bad rows
   INSERT INTO orders (id, amount, created_at)
   SELECT id::integer,
          translate(amount, '$,', '')::numeric,
          created_at::date
   FROM import_stage
   WHERE id ~ '^\d+$'
     AND amount ~ '^\$?[\d,]+(\.\d+)?$'
     AND created_at ~ '^\d{4}-\d{2}-\d{2}$';
   

Additional Information

• SQLSTATE 22018 is defined in the SQL standard under the Data Exception class. PostgreSQL has implemented it since early versions; no specific version change is notable for this condition.
• Related SQLSTATE codes in class 22:
  • 22003 — numeric_value_out_of_range: the string is a valid number but exceeds the target type's range.
  • 22007 — invalid_datetime_format: a string cannot be parsed as a date/time type.
  • 22P02 — invalid_text_representation: a PostgreSQL-specific variant raised when the input text is not a valid representation (functionally very similar to 22018 and often seen in the same scenarios with types like uuid, boolean, inet).
• In practice, PostgreSQL most commonly reports 22P02 (invalid_text_representation) rather than 22018 for many built-in types, because the error is generated by the type's input function using a slightly different error code path. The two are closely related and the fixes are identical.
• JDBC drivers surface this as PSQLException with getSQLState() returning "22018" or "22P02". The psycopg2 Python driver exposes psycopg2.errors.InvalidCharacterValueForCast and psycopg2.errors.InvalidTextRepresentation as distinct exception subclasses.
• There are no performance implications specific to this error — it is a hard failure raised immediately upon the failed cast, before any data is written.

Frequently Asked Questions

Why do I see 22P02 instead of 22018 for what seems like the same error?

Both codes indicate that a string could not be parsed as the target type, and PostgreSQL raises one or the other depending on how the type's input function signals the error internally. 22P02 (invalid_text_representation) is a PostgreSQL extension to the SQL standard and is used by most built-in types (integer, UUID, boolean, inet, etc.). 22018 (invalid_character_value_for_cast) is the SQL-standard code. For practical purposes, the diagnosis and fix are identical.

Is there a built-in TRY_CAST function in PostgreSQL like in SQL Server?

No, PostgreSQL does not have a TRY_CAST built-in. The recommended approach is to write a small PL/pgSQL helper function that catches the exception and returns NULL, as shown in the fix section above. Some extensions and frameworks provide this utility, but it is straightforward to implement yourself.

How do I find which rows in a table contain values that will fail a cast?

Use a safe-cast function (see above) to identify problematic rows before attempting a bulk cast or migration:

SELECT raw_value
FROM import_stage
WHERE safe_cast_integer(raw_value) IS NULL
  AND raw_value IS NOT NULL;

This returns all non-NULL rows whose values cannot be cast to integer.

Does this error abort my entire transaction or just the current statement?

It aborts the current statement and places the transaction into an error state. All subsequent statements in the same transaction block will be ignored until you ROLLBACK (or roll back to a SAVEPOINT). To continue processing other rows despite cast failures, use a savepoint per row or perform validation in a separate pass before the actual insert/update.