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
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
''intoNULLor 0. - Malformed input for
uuid,json/jsonb,boolean,inetor an array literal. - Bulk
COPYor CSV loads where one row has text in a numeric column.
How to fix it
-
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');
-
On older versions, filter with a pattern before casting.
SELECT raw_value::int FROM staging WHERE raw_value ~ '^-?[0-9]+$';
-
Turn empty strings into
NULLexplicitly.SELECT NULLIF(trim(raw_value), '')::int FROM staging;
-
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
22003numeric value out of range22007invalid datetime format42804datatype mismatch22021invalid byte sequence for encoding245SQL Server: Conversion failed converting a value1366MySQL: Incorrect value for columnORA-01722Oracle: invalid number
More data exception errors
22008date/time field value out of range22012division by zero22023invalid parameter value22001value too long for type
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.
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.