invalid_text_representation invalid input syntax for type integer: “…” — 22P02

Symptoms

A text value could not be parsed as an integer. PostgreSQL tried to convert a string to a 4-byte integer and the string was not a valid whole number.

• The statement aborts with SQLSTATE 22P02; the offending text is shown in quotes.
• Triggered by decimals, letters, empty strings, or formatting characters.
• Often an application binding the wrong type to an integer column or parameter.

What the server log shows

ERROR:  invalid input syntax for type integer: "12.5"
LINE 1: SELECT '12.5'::integer;
               ^
STATEMENT:  SELECT '12.5'::integer;

Why PostgreSQL raises this — what the manual says

Section 8.1.1 Integer Types:

“The types smallint, integer, and bigint store whole numbers, that is, numbers without fractional components, of various ranges.”

The integer input function accepts only an optional sign and digits. Anything else — a decimal point, letters, currency symbols, thousands separators, whitespace inside the value, or an empty string — fails with 22P02. The offending text is shown in quotes in the message.

Common causes

• Sending a decimal ('12.5') where an integer is expected.
• Empty strings or non-numeric text bound to an integer column/parameter.
• Thousands separators or currency symbols in the value.
• A comparison WHERE id = 'abc' forcing a string-to-integer cast.

How to fix it

1. Send a clean integer literal, or cast a decimal first: '12.5'::numeric::integer (rounds).
2. Validate/trim input in the application before binding.
3. Use the right column type — numeric for decimals, text for codes.
4. For tolerant parsing, check the value matches ^-?\d+$ first.

Related & next steps

• SQLSTATE 22P02 — Invalid Text Representation (full reference)
• Message: invalid input syntax for type numeric
• Message: integer out of range

Reference: PostgreSQL 18 Section 8.1.1 “Integer Types”.