with constraints as (select distinct
o.conname, 
(select nspname from pg_namespace where oid=c.relnamespace) as table_schema,
c.relname as table_name,
case when o.contype='c' then 'CHECK'
when o.contype='f' then 'FOREIGN KEY' 
when contype='t' THEN 'CONSTRAINT TRIGGER'
end as constraint_type,
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_class c on c.oid = o.conrelid
where c.relkind in ('r','f') 
and o.contype in ('f', 'c')
and o.conislocal=true)
select table_schema, constraint_type, conname, count(*) AS number_of_occurrences, string_agg(table_type || '.' || table_schema || '.' || table_name, ';<br>' order by table_schema, table_name) as tables
from constraints
group by table_schema, constraint_type, conname
having count(*)>1
order by count(*) desc, table_schema, constraint_type;