Candidate keys and foreign keys of tables that participate in an inheritance hierarchies

with constr as (select 
o.conname,
(select nspname from pg_namespace where oid=c.relnamespace) as constr_schema,
c.relname as constr_table, 
c.oid as constr_table_oid,
o.conkey AS constr_col,
case when o.contype='p' then 'PRIMARY KEY' 
when o.contype='u' then 'UNIQUE' 
when o.contype='f' then 'FOREIGN KEY' 
else 'EXCLUDE' END as contype,
exists (select 1 from pg_inherits as i where c.oid=i.inhparent) and not exists (select 1 from pg_inherits as i where c.oid=i.inhrelid) as is_root
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('p','u', 'f', 'x') and c.oid in (select inhrelid from pg_inherits union select inhparent from pg_inherits) ),
constr_unnest as (select conname, constr_schema, constr_table, constr_table_oid, constr_col, constr_col_num, ordin, contype, is_root
from constr, unnest(constr.constr_col) with ordinality as k(constr_col_num, ordin)),
constr_with_names as (select conname, constr_schema, constr_table, contype, array_agg(a_key.attname order by ordin) as constr_col, is_root
from constr_unnest k inner join pg_attribute a_key on k.constr_col_num = a_key.attnum and k.constr_table_oid = a_key.attrelid and a_key.attisdropped = false
group by conname, constr_schema, constr_table, contype, is_root)
select conname, constr_schema, constr_table, contype, constr_col, is_root
from constr_with_names
order by constr_schema, constr_table, contype, constr_col;