How to fix Invalid character value for cast in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 26, 2025· 217 views

This error occurs when PostgreSQL cannot convert a value to the target data type due to incompatible characters or format. It commonly happens during type conversions with the CAST operator or :: syntax when the source data contains invalid characters for the target type.

Quick fix

Fixes Invalid character value for cast

-- This will fail:
SELECT CAST('abc123' AS INTEGER);
-- ERROR: invalid input syntax for integer: "abc123"
  1. Identify the problematic query and data
  2. Validate and clean data before casting
  3. Use TRY_CAST for safe casting (PostgreSQL 16+)
  4. Create a safe cast wrapper for older PostgreSQL versions
postgresql — error
-- This will fail:
SELECT CAST('abc123' AS INTEGER);
-- ERROR: invalid input syntax for integer: "abc123"
Invalid character value for cast

What this error means

The "Invalid character value for cast" error (PostgreSQL error code 22018) occurs when you attempt to cast or convert a value from one data type to another, but the value contains characters that are incompatible with the target type. For example, trying to convert the string "abc123" to an integer fails because the letters cannot be part of an integer. This is a data validation error—PostgreSQL is protecting your database from accepting malformed data that does not match the expected type format. The error typically appears during operations like INSERT, UPDATE, or SELECT queries where type conversions are explicitly or implicitly requested through CAST(), the :: operator, or ALTER TABLE operations.

Typical symptoms

  • Queries fail with error code 22018 during INSERT or UPDATE operations
  • Type casting with CAST() or :: operator fails with an "invalid character" message
  • SELECT queries fail when attempting to convert columns with mixed or malformed data
  • ALTER TABLE column type changes fail midway through the operation
  • String-to-numeric or string-to-date conversions fail unexpectedly

Common causes

  1. Attempting to cast a string containing non-numeric characters (like "123abc") to an integer or numeric type
  2. Casting a string with invalid date format (like "2023-02-30" or "invalid-date") to a DATE or TIMESTAMP type
  3. Casting empty strings or whitespace-only strings to numeric types without handling them first
  4. Decimal separators or locale-specific formatting mismatches (e.g., comma vs. period in numbers)
  5. Data in a column contains unexpected formats before altering the column type
  6. NULL values being cast to types that do not accept NULL without proper handling

How to fix "Invalid character value for cast"

1Identify the problematic query and data

Check your PostgreSQL logs or application error output to find the exact query causing the error. Look for the CAST operation or :: syntax involved. For example:

sql
-- This will fail:
SELECT CAST('abc123' AS INTEGER);
-- ERROR: invalid input syntax for integer: "abc123"

Once you identify the query, examine the source data to understand what values are causing the issue.

2Validate and clean data before casting

Use PostgreSQL functions to validate and clean data. For numeric conversions, check if the string contains only digits:

sql
-- Check for non-numeric characters
SELECT * FROM users WHERE phone ~ '[^0-9]';

-- Remove non-numeric characters before casting
SELECT CAST(REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS INTEGER) FROM users;

For date conversions, validate the format:

sql
-- Check if string matches date format
SELECT * FROM events WHERE date_str !~ '\d{4}-\d{2}-\d{2}';
3Use TRY_CAST for safe casting (PostgreSQL 16+)

PostgreSQL 16 introduced TRY_CAST, which returns NULL instead of raising an error when a cast fails:

sql
-- This returns NULL instead of erroring
SELECT TRY_CAST('abc123' AS INTEGER);
-- Result: NULL

-- Use with COALESCE to provide a default
SELECT COALESCE(TRY_CAST(value AS INTEGER), 0) FROM my_table;

If you are on PostgreSQL 16+, this is the recommended approach.

4Create a safe cast wrapper for older PostgreSQL versions

For PostgreSQL versions before 16, create a custom function that handles cast errors:

sql
-- Safe integer casting function
CREATE OR REPLACE FUNCTION safe_cast_to_int(text)
RETURNS INTEGER AS $$
BEGIN
  RETURN CAST($1 AS INTEGER);
EXCEPTION WHEN OTHERS THEN
  RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Use it like TRY_CAST
SELECT safe_cast_to_int('abc123');
-- Result: NULL
5Use CASE expressions for conditional casting

For more complex logic, use CASE to validate before casting:

sql
-- Only cast if the string looks numeric
SELECT 
  CASE 
    WHEN phone ~ ''^ [0-9]{10}$'' THEN CAST(phone AS BIGINT)
    ELSE NULL
  END AS phone_id
FROM users;

-- For dates, validate format first
SELECT
  CASE
    WHEN date_str ~ '\d{4}-\d{2}-\d{2}' THEN CAST(date_str AS DATE)
    ELSE NULL
  END AS event_date
FROM events;
6Handle NULL values explicitly

NULL values can cause casting issues. Handle them before casting:

sql
-- Remove or replace NULL before casting
SELECT CAST(COALESCE(age, '0') AS INTEGER) FROM users;

-- Or use NULLIF to skip empty strings
SELECT CAST(NULLIF(age, '') AS INTEGER) FROM users;
7Fix column type migrations with USING clause

When altering a table column type fails due to casting issues, use the USING clause to explicitly transform the data:

sql
-- This will fail if column contains non-numeric data:
ALTER TABLE orders ALTER COLUMN quantity TYPE INTEGER;

-- Use USING to safely convert with validation:
ALTER TABLE orders 
ALTER COLUMN quantity TYPE INTEGER 
USING CASE 
  WHEN quantity ~ ''^ [0-9]+$'' THEN CAST(quantity AS INTEGER)
  ELSE 0
END;

-- Or with TRY_CAST (PostgreSQL 16+):
ALTER TABLE orders 
ALTER COLUMN quantity TYPE INTEGER 
USING TRY_CAST(quantity AS INTEGER);

Advanced notes

Locale and collation settings can affect casting behavior. Some locales use different decimal separators (comma in Europe vs. period in US). If you encounter casting issues with numeric data from different regions, check the locale settings with SELECT current_setting('lc_numeric'); and consider using TO_NUMBER() with a format string instead of direct CAST:

sql
SELECT TO_NUMBER('1.234,56', '9G999D99') FROM my_table;

For performance-critical operations, pre-validate data at the application layer before sending to PostgreSQL to avoid repeated casting errors. Additionally, indexes on text columns being frequently cast can slow performance; consider storing normalized data types directly when possible rather than casting frequently.

Frequently asked questions

What does "Invalid character value for cast" mean?
The "Invalid character value for cast" error (PostgreSQL error code 22018) occurs when you attempt to cast or convert a value from one data type to another, but the value contains characters that are incompatible with the target type. For example, trying to convert the string "abc123" to an integer fails because the letters cannot be part of an integer. This is a data validation error—PostgreSQL is protecting your database from accepting malformed data that does not match the expected type format.
How do I fix "Invalid character value for cast"?
This error occurs when PostgreSQL cannot convert a value to the target data type due to incompatible characters or format. It commonly happens during type conversions with the CAST operator or :: syntax when the source data contains invalid characters for the target type.
What causes "Invalid character value for cast"?
Attempting to cast a string containing non-numeric characters (like "123abc") to an integer or numeric type Casting a string with invalid date format (like "2023-02-30" or "invalid-date") to a DATE or TIMESTAMP type Casting empty strings or whitespace-only strings to numeric types without handling them first
Is the "Invalid character value for cast" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading