The number of constraints by schema, by type, and in total

Copy SQL

WITH constraints AS (select 
n.nspname as schema,
c.relname,
o.conname, 
CASE WHEN o.contype ='c' THEN 'TABLE CHECK'
WHEN o.contype ='f' THEN 'FOREIGN KEY'
WHEN o.contype ='p' THEN 'PRIMARY KEY'
WHEN o.contype ='u' THEN 'UNIQUE'
WHEN o.contype ='t' THEN 'CONSTRAINT TRIGGER'
WHEN o.contype ='x' THEN 'EXCLUSION' END AS con_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
where c.relkind = 'r' 
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)
union select n.nspname as schema,
t.typname,
o.conname, 
'DOMAIN CHECK' AS con_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
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))
SELECT schema, con_type, Count(*) AS number_of_constraints
FROM constraints
GROUP BY CUBE (schema, con_type)
ORDER BY schema, con_type, Count(*) DESC;