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;