SQLSTATE 22P02 ERROR Class 22: Data Exception

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

PostgreSQL error "invalid input syntax for type boolean: "…"" (SQLSTATE 22P02): what it means, common causes, and how to fix it.

PG 12, 13, 14, 15, 16, 17, 18 Official docs
Last reviewed May 2025 Grounded in source

Diagnostic Queries

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

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

Related & next steps

Reference: PostgreSQL 18 Section 8.6 “Boolean Type”.

Was this helpful?