Why does information_schema have “YES” and “NO” character strings rather than booleans?

Question

I was taken by surprise to find that the is_nullable column for this query is typed as character varying (3) rather than boolean:

select
    column_name,
    data_type,
    is_nullable
from
    information_schema.columns
where
    table_schema = 'public';

I noticed that some other column use "YES"/"NO" as well. What is the rationale for this? My initial thought was that perhaps other values besides "YES" and "NO" are possible, justifying something other than a boolean, but I didn't see any examples of this in my current database.

Answer 1

INFORMATION_SCHEMA

Using psql you can see the schema with \d information_schema.columns, or you can look it up in the docs.

                               View "information_schema.columns"
          Column          |                Type                | Collation | Nullable | Default 
--------------------------+------------------------------------+-----------+----------+---------
... stuff cropped..
 is_nullable              | information_schema.yes_or_no       |           |          | 

SQL Spec

Looking up information_schema.columns.is_nullable in the spec we can see it's the spec's fault -- as is often the case with stuff. You can't just erase 9,000 years of legacy. Especially with something that is supposed to provide a dependable interface. From the SQL 2011 spec,

IS_NULLABLE INFORMATION_SCHEMA.YES_OR_NO
  CONSTRAINT COLUMNS_IS_NULLABLE_NOT_NULL NOT NULL,

And you can see the definition of INFORMATION_SCHEMA.YES_OR_NO defined in the spec too,

CREATE DOMAIN YES_OR_NO AS
  CHARACTER VARYING (3)
  CHARACTER SET SQL_IDENTIFIER
    CONSTRAINT YES_OR_NO_CHECK
    CHECK (VALUE IN ( 'YES', 'NO' ) );

GRANT USAGE ON DOMAIN YES_OR_NO
  TO PUBLIC WITH GRANT OPTION;

PostgreSQL's RDBMs-specific catalog

If you don't need the generic standardized interface though, you can usually find something more sane in pg_catalog. In this case you want pg_catalog.pg_attribute. Let's check it out \d pg_catalog.pg_attribute,

              Table "pg_catalog.pg_attribute"
    Column     |   Type    | Collation | Nullable | Default 
---------------+-----------+-----------+----------+---------
 attnotnull    | boolean   |           | not null | 

There is your boolean.

PostgreSQL Docs

The PostgreSQL docs on Information Schema: Data Types actually address this too,

A character string domain that contains either YES or NO. This is used to represent Boolean (true/false) data in the information schema. (The information schema was invented before the type boolean was added to the SQL standard, so this convention is necessary to keep the information schema backward compatible.)