The list of all the queries

Perhaps the type of a base table column/domain should be BOOLEAN (based on CHECK constraints)

Query goal: Find base table columns and domains that have a CHECK constraint that limits possible values in a manner that seems to indicate that the permitted values represent truth values, i.e., permitted values are 0/1 or true/false.
Notes about the query: Find base table columns that have not type BOOLEAN and have a CHECK constraint that refers to a possibility that the task of the column is to record Boolean values. For instance, the type of a column could be SMALLINT and the column has a CHECK constraint that determines that possible values in the column are 0 and 1.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: Medium (Medium number of false-positive results)
Query license: MIT License
Fixing suggestion: Specify for each column a right data type that takes into account expected values in the column. Use Boolean type instead of emulating it based on some other data type. Change the type of the column to BOOLEAN. Drop the check constraints.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

WITH expressions AS (select 
n.nspname as schema,
c.relname || '.' || o.conname as name, 
substring(pg_get_constraintdef(o.oid),7) as expression,
'TABLE CHECK' as constraint_type,
at.typname as data_type
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace n on n.oid=c.relnamespace
inner join pg_attribute a on a.attnum=o.conkey[1] and a.attrelid=c.oid
inner join pg_type at on a.atttypid=at.oid
where o.contype ='c' 
and cardinality(o.conkey)=1
and n.nspname not in (select schema_name
from information_schema.schemata
where schema_name<>'public' and
schema_owner='postgres' and schema_name is not null) and at.typname<>'bool'
union select n.nspname as schema,
t.typname || '.' || o.conname as name, 
substring(pg_get_constraintdef(o.oid),7) as expression,
'DOMAIN CHECK' as constraint_type,
dt.typname as data_type
from pg_constraint o inner join pg_type t on t.oid = o.contypid
inner join pg_namespace n on n.oid=t.typnamespace
inner join pg_type dt on t.typbasetype=dt.oid
where o.contype ='c' and n.nspname not in (select schema_name
from information_schema.schemata
where schema_name<>'public' and
schema_owner='postgres' and schema_name is not null) and dt.typname<>'bool')
SELECT schema, name, expression, constraint_type, data_type
FROM expressions
WHERE expression ~*'[[:space:]]+ANY[[:space:]]+[(]ARRAY[[][^,]*(true|false|0|1)[^,]*,[^,]*(true|false|0|1)[^,]*[]][)]' 
OR expression ~*'=[[:space:]]+(0|1)[)][[:space:]]+OR[[:space:]]+.+=[[:space:]]+(0|1)' 
OR expression ~*'(true|false)[^,]*[[:space:]]OR[[:space:]][^,]*(true|false)' 
ORDER BY schema, constraint_type, name;

Collections where the query belongs to

Collection nameCollection description
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

Categories where the query belongs to

Category nameCategory description
Boolean dataQueries of this category provide information about truth-values data that is kept in the database.
CHECK constraintsQueries of this category provide information about CHECK constraints.
Data typesQueries of this category provide information about the data types and their usage.
DomainsQueries of this category provide information about reusable specifications of column properties.
Validity and completenessQueries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness).

The list of all the queries