// PostgreSQL error reference

PostgreSQL 22P02: invalid input syntax for type

A text value couldn't be converted to the target type: letters into an integer, a malformed UUID, bad JSON, an empty string into a number.

SQLSTATE
22P02
Name
invalid_text_representation
Category
Data Exception
Error message
invalid input syntax for type integer: "abc"

What it means

PostgreSQL converts literals and text to typed values with each type's input function. When the text isn't a valid representation of the type, the conversion fails with 22P02 and the message quotes the offending value.

The value can come from a literal in the query, a bound parameter, a CAST, or a column of text being compared with or inserted into a typed column.

Common causes

  • Comparing a numeric or UUID column with a string that isn't a number or UUID, for example WHERE id = 'abc' or an empty string ''.
  • Passing an empty string where a number is expected. PostgreSQL does not turn '' into NULL or 0.
  • Malformed input for uuid, json/jsonb, boolean, inet or an array literal.
  • Bulk COPY or CSV loads where one row has text in a numeric column.

How to fix it

  1. Find rows that wouldn't convert before casting them (PostgreSQL 16 and later have pg_input_is_valid).

    SELECT raw_value
    FROM staging
    WHERE NOT pg_input_is_valid(raw_value, 'integer');
  2. On older versions, filter with a pattern before casting.

    SELECT raw_value::int
    FROM staging
    WHERE raw_value ~ '^-?[0-9]+$';
  3. Turn empty strings into NULL explicitly.

    SELECT NULLIF(trim(raw_value), '')::int FROM staging;
  4. In application code, bind parameters with the right type instead of building SQL strings, and validate input before it reaches the database.

Reproduce it safely

SELECT 'abc'::int;  -- ERROR: invalid input syntax for type integer: "abc"

Related errors

More data exception errors

The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.

// fewer errors, faster fixes

Work on PostgreSQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.