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.)