The list of all the queries

CHECK constraints on columns with Boolean data

Query goal: Find check constraints that involve columns with the type Boolean.
Query type: General (Overview of some aspect of the database.)
Query license: MIT License
Data source: INFORMATION_SCHEMA only
SQL query: Click on query to copy it

WITH checks AS (SELECT ccu.table_schema, ccu.table_name, t.table_type, ccu.column_name, cc.check_clause, cc.constraint_name, 'TABLE CHECK' AS check_type, c.data_type
FROM INFORMATION_SCHEMA.constraint_column_usage AS ccu INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name)
INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
INNER JOIN INFORMATION_SCHEMA.columns c USING (table_schema, table_name, column_name)
WHERE  c.data_type='boolean' 
AND cc.check_clause NOT LIKE '%IS NOT NULL' 
AND ccu.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT cdu.table_schema, cdu.table_name, t.table_type, cdu.column_name, cc.check_clause, cc.constraint_name, 'DOMAIN CHECK' AS check_type, c.data_type
FROM INFORMATION_SCHEMA.column_domain_usage AS cdu INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
INNER JOIN INFORMATION_SCHEMA.domain_constraints AS dc USING (domain_schema, domain_name)
INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name)
INNER JOIN information_schema.columns c USING (table_schema, table_name, column_name)
WHERE c.data_type='boolean' 
AND t.table_type IN ('BASE TABLE','FOREIGN') 
AND cc.check_clause NOT LIKE '%IS NOT NULL'  
AND cdu.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),
temp_columns AS (SELECT c.table_schema, c.table_name, t.table_type, c.column_name, c.data_type, '' AS check_clause, '' AS check_type
FROM information_schema.tables t INNER JOIN information_schema.schemata s ON t.table_schema=s.schema_name
INNER JOIN information_schema.columns c USING (table_schema, table_name)
WHERE c.data_type='boolean' 
AND table_type IN ('BASE TABLE','FOREIGN')
AND (T.table_schema = 'public' OR S.schema_owner<>'postgres'))
SELECT table_schema, table_name, table_type, column_name, data_type, check_clause, check_type
FROM checks
UNION SELECT table_schema, table_name, table_type, column_name, data_type, check_clause, check_type
FROM temp_columns AS tc
WHERE NOT EXISTS (SELECT 1
FROM checks AS ch
WHERE ch.table_schema=tc.table_schema AND ch.table_name=tc.table_name AND ch.column_name=tc.column_name)
ORDER BY table_schema, table_name, column_name;

Collections where the query belongs to

Collection nameCollection description
Find problems about integrity constraintsA selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems 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.
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