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
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.
Type
Problem detection (Each row in the result could represent a flaw in the design)
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
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
This query belongs to the following collections:
Name
Description
Find problems automatically
Queries, 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
This query is classified under the following categories:
Name
Description
Boolean data
Queries of this category provide information about truth-values data that is kept in the database.
CHECK constraints
Queries of this category provide information about CHECK constraints.
Data types
Queries of this category provide information about the data types and their usage.
Domains
Queries of this category provide information about reusable specifications of column properties.
Validity and completeness
Queries 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).