with constr as (select
o.conname as constraint_name,
(select nspname from pg_namespace where oid=c.relnamespace) as supertable_schema,
c.relname as supertable,
c.oid as supertable_oid,
o.conkey AS constraint_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='x' THEN 'EXCLUDE' END AS constraint_type
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('f','p','u', 'x')),
constr_unnest as (select constraint_name, constraint_type, supertable_schema, supertable, supertable_oid, constraint_col, constraint_col_num, ordin
from constr, unnest(constr.constraint_col) with ordinality as f(constraint_col_num, ordin)),
constr_with_names as (select constraint_name, constraint_type, supertable_schema, supertable, supertable_oid, array_agg(a_target.attname order by ordin) as constraint_col 
from constr_unnest constr inner join pg_attribute a_target on constr.constraint_col_num = a_target.attnum and constr.supertable_oid = a_target.attrelid and a_target.attisdropped = false
group by constraint_name, constraint_type, supertable_schema, supertable, supertable_oid),
supertable_constraints as (select constraint_name, constraint_type, supertable_schema, supertable, constraint_col, pc.nspname as subtable_schema, c.relname as subtable_name
from constr_with_names inner join pg_inherits pi on pi.inhparent=constr_with_names.supertable_oid
inner join pg_class c on pi.inhrelid=c.oid
inner join pg_namespace pc on c.relnamespace=pc.oid)
SELECT constraint_name, constraint_type, supertable_schema, supertable, constraint_col, subtable_schema, subtable_name
FROM supertable_constraints as sc
WHERE EXISTS (SELECT 1
FROM constr_with_names AS cn
WHERE sc.subtable_schema=cn.supertable_schema 
AND sc.subtable_name=cn.supertable
AND sc.constraint_type=cn.constraint_type
AND sc.constraint_col=cn.constraint_col)
order by supertable_schema, supertable, constraint_name;