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,
regexp_replace(pg_get_constraintdef(o.oid),'( NO INHERIT)','') AS constraint_def,
o.connoinherit
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype='c'),
constr_unnest as (select constraint_name, supertable_schema, supertable, supertable_oid, constraint_col, constraint_col_num, ordin, constraint_def, connoinherit
from constr, unnest(constr.constraint_col) with ordinality as f(constraint_col_num, ordin)),
constr_with_names as (select constraint_name, supertable_schema, supertable, supertable_oid, array_agg(a_target.attname order by ordin) as constraint_col , constraint_def, connoinherit
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, supertable_schema, supertable, supertable_oid, constraint_def, connoinherit),
supertable_constraints as (select constraint_name, supertable_schema, supertable, constraint_col, pc.nspname as subtable_schema, c.relname as subtable_name, constraint_def, connoinherit
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 sc.supertable_schema, sc.supertable, sc.constraint_col, sc.constraint_def, subtable_schema, subtable_name, sc.constraint_name AS supertable_constraint_name,  cn.constraint_name AS subtable_constraint_name
FROM supertable_constraints as sc, constr_with_names AS cn
WHERE sc.subtable_schema=cn.supertable_schema 
AND sc.subtable_name=cn.supertable
AND sc.constraint_def=cn.constraint_def
AND sc.connoinherit='true'
order by sc.supertable_schema, sc.supertable, sc.constraint_col;