invalid input syntax for type boolean: “…”

SQLSTATE 22P02 condition invalid_text_representation class 22 — Data Exception severity ERROR

Reproduced & verified on PostgreSQL 14.23, 15.18, 16.14, 17.10 and 18.4 — identical message on every version.

Last reviewed 30 May 2025 · Reproduced live with the SQL on this page.

Symptoms

A value being converted to boolean is not a recognized boolean literal. PostgreSQL raises SQLSTATE 22P02 (invalid_text_representation).

Accepted inputs include t/f, true/false, yes/no, on/off, 1/0.
Values like Y, 2, or empty string are rejected.
Common in COPY/import and parameter binding.

What the server log shows

ERROR:  invalid input syntax for type boolean: "Y"

Why PostgreSQL raises this — what the manual says

Section 8.6 Boolean Type:

“Boolean constants can be represented in SQL queries by the SQL key words TRUE, FALSE, and NULL.”

The boolean input function only recognizes a fixed set of case-insensitive literals (and their unique prefixes). Anything outside that set has no boolean meaning and fails with 22P02.

Common causes

Passing Y/N or 2 instead of a recognized literal.
Empty string for a missing boolean.
Importing data that encodes booleans differently.

How to fix it

Map values to accepted literals (true/false, t/f, 1/0) before insert.
Use a CASE expression to translate custom codes: CASE flag WHEN 'Y' THEN true ELSE false END.
Convert empty strings to NULL.

Related & next steps

Reference: PostgreSQL 18 Section 8.6 “Boolean Type”.