WITH base_tables_with_check_constraints AS (SELECT ccu.table_schema, ccu.table_name
FROM INFORMATION_SCHEMA.constraint_column_usage AS ccu INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc
USING (constraint_schema, constraint_name)
WHERE 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
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_catalog, constraint_schema, constraint_name)
WHERE t.table_type='BASE TABLE' 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 catalog_name IS NOT NULL AND schema_name IS NOT NULL)),
base_tables AS (SELECT A.table_schema, A.table_name
FROM INFORMATION_SCHEMA.tables A
INNER JOIN INFORMATION_SCHEMA.schemata B
ON A.table_schema=B.schema_name
WHERE A.table_type='BASE TABLE'
AND (A.table_schema = 'public'
OR B.schema_owner<>'postgres')),
number_of_base_tables AS (SELECT Count(*) AS number_of_base_tables
FROM base_tables),
number_of_base_tables_with_checks AS (SELECT Count(*) AS number_of_base_tables_with_checks
FROM base_tables_with_check_constraints)
SELECT (number_of_base_tables-number_of_base_tables_with_checks) AS number_of_base_tables_without_checks,
number_of_base_tables_with_checks,
number_of_base_tables,
Round((number_of_base_tables-number_of_base_tables_with_checks)::decimal*100/number_of_base_tables::decimal,1) AS percentage_of_base_tables_without_checks
FROM number_of_base_tables, number_of_base_tables_with_checks
WHERE EXISTS (SELECT * FROM base_tables);