EC
22P02
PostgreSQLERRORCommonData ExceptionHIGH confidence

invalid input syntax for type integer

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
  1. 1Passing a non-numeric string where an integer is expected (e.g., "abc" in a URL parameter routed to an integer column)
  2. 2Locale-formatted number strings with commas as thousands separators (e.g., "1,000")
  3. 3Empty string passed to an integer parameter
  4. 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
trigger — this will error
SELECT '1,000'::INTEGER;

expected output

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

Fix 1

Clean the input before casting

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

Clean the input before casting
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 When the input may or may not be a valid integer and NULL is an acceptable fallback.

Validate with a safe cast returning NULL on failure
SELECT CASE
  WHEN '1,000' ~ '^-?[0-9]+
#x27; 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

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 documentation ↗

src/backend/utils/adt/int.c — int4in()

Numeric Types ↗

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

← All PostgreSQL errors