22P02ERRORTier 1 — Safe✅ HIGH confidence

invalid input syntax for type integer

Category: Data ExceptionVersions: All Postgres versions

What this means

Postgres attempted to convert a text string to a numeric type (integer, bigint, numeric, etc.) and the string is not a valid representation of that type. The cast fails before any data is written.

Why it happens

1Passing a non-numeric string where an integer is expected (e.g., "abc" in a URL parameter routed to an integer column)
2Locale-formatted number strings with commas as thousands separators (e.g., "1,000")
3Empty string passed to an integer parameter
4Implicit cast from TEXT to INTEGER in a query where the column type is integer

How to reproduce

A text value that cannot be parsed as an integer is cast to INTEGER.

trigger — this will ERROR

SELECT '1,000'::INTEGER;

ERROR: invalid input syntax for type integer: "1,000" LINE 1: SELECT '1,000'::INTEGER;

Fix 1: Clean the input before casting

When the string has formatting characters (commas, currency symbols) that need stripping.

fix

SELECT REGEXP_REPLACE('1,000', '[^0-9\-]', '', 'g')::INTEGER; -- returns 1000

Why this works

REGEXP_REPLACE strips non-numeric characters before the cast is attempted. The ::INTEGER cast then receives a clean string that the integer input function (int4in) can parse successfully.

Fix 2: Validate with a safe cast returning NULL on failure

When the input may or may not be a valid integer and NULL is an acceptable fallback.

fix

SELECT CASE
  WHEN '1,000' ~ '^-?[0-9]+
    
    
    
  

 THEN '1,000'::INTEGER
  ELSE NULL
END;

-- Postgres 14+: cleaner with a helper function
CREATE OR REPLACE FUNCTION try_cast_int(p_text TEXT) RETURNS INTEGER AS $
BEGIN
  RETURN p_text::INTEGER;
EXCEPTION WHEN invalid_text_representation THEN
  RETURN NULL;
END;
$ LANGUAGE plpgsql;

Why this works

The regex pre-check validates the format before the cast. The PL/pgSQL function catches the exception (SQLSTATE 22P02 maps to invalid_text_representation) and returns NULL, mimicking TRY_CAST behaviour from other databases.

What not to do

✗

Catch 22P02 and silently substitute 0

Why it's wrong: Zero is a valid integer that may have business meaning; substituting it silently corrupts calculations. Return NULL or raise an application-level error instead.

Sources

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

📚 Feature docs: https://www.postgresql.org/docs/current/datatype-numeric.html

🔧 Source ref: src/backend/utils/adt/int.c — int4in()

📖 Further reading: Numeric Types

Confidence assessment

✅ HIGH confidence

Stable and well-documented. The int4in() input function behaviour is consistent across all versions. Edge case: the error message varies slightly by target type (integer vs bigint vs numeric), but SQLSTATE is always 22P02.