Number of columns covered with constraints

Copy SQL

WITH constr AS (SELECT 
nc.nspname AS constr_schema,
o.conrelid AS constr_table, 
unnest(o.conkey) AS constr_col,
CASE WHEN o.contype='p' THEN 'PRIMARY KEY'
WHEN o.contype='u' THEN 'UNIQUE'
WHEN o.contype='f' THEN 'FOREIGN KEY'
WHEN o.contype='c' THEN 'TABLE CHECK'
WHEN o.contype='x' THEN 'EXCLUDE'
WHEN o.contype='t' THEN 'CONSTRAINT TRIGGER' END AS constr_type
FROM pg_constraint o INNER JOIN pg_class c on c.oid = o.conrelid
inner join pg_namespace as nc on nc.oid=c.relnamespace
inner join  pg_authid AS a on nc.nspowner=a.oid
where (nc.nspname='public' or rolname<>'postgres')),
nr_of_all_cols AS (SELECT Count(*)::numeric AS nr_of_all_cols
FROM information_schema.columns AS c INNER JOIN INFORMATION_SCHEMA.tables AS t USING  (table_schema, table_name) 
WHERE c.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) 
AND table_type='BASE TABLE')
SELECT constr_schema, constr_type, Count(DISTINCT constr_table::text || '.'|| constr_col::text) AS nr_of_columns, Round(Count(DISTINCT constr_table::text || '.'|| constr_col::text)::numeric*100/(SELECT nr_of_all_cols FROM nr_of_all_cols),1) AS percentage_of_columns
FROM constr
GROUP BY CUBE (constr_schema, constr_type);