WITH constraints AS (SELECT conname, nspname AS table_schema, 
c.relname as table_name,
CASE 
WHEN contype='c' THEN 'CHECK'
WHEN contype='f' THEN 'FOREIGN KEY'
WHEN contype='p' THEN 'PRIMARY KEY'
WHEN contype='u' THEN 'UNIQUE'
WHEN contype='x' THEN 'EXCLUDE'
END AS contype,
CASE WHEN c.relkind='r' then 'BASE TABLE'
WHEN c.relkind='f' then 'FOREIGN TABLE' 
END AS table_type
FROM pg_constraint o INNER JOIN pg_namespace n ON o.connamespace=n.oid
INNER JOIN pg_class c on c.oid = o.conrelid
WHERE (substring(pg_get_constraintdef(o.oid),7) NOT LIKE '%NOT NULL' OR substring(pg_get_constraintdef(o.oid),7) IS NULL) 
AND c.relkind IN  ('r', 'f')
AND o.contype<>'t')
SELECT conname, Count(*) AS number_of_appearances, string_agg(table_type || '.' || table_schema || '.' || table_name || ' (' || contype || ')',';
' ORDER BY table_schema, table_name, contype) AS tables
FROM constraints
GROUP BY conname
HAVING Count(*)>1
ORDER BY Count(*) DESC;