with keys as (select 
o.conname, 
nc.nspname as key_schema,
c.relname as key_table, 
c.oid as key_table_oid,
o.conkey AS key_col,
case when o.contype='p' then 'PRIMARY KEY' ELSE 'UNIQUE' END as contype
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace as nc on nc.oid=c.relnamespace
inner join  pg_authid AS a on nc.nspowner=a.oid
where (nc.nspname='public' or rolname<>'postgres')
and o.contype in ('u', 'p')),
keys_unnest as (select conname, key_schema, key_table, key_table_oid, key_col, key_col_num, ordin, contype
from keys, unnest(keys.key_col) with ordinality as k(key_col_num, ordin)),
keys_with_names as (select conname, key_schema, key_table, contype, array_agg(a_key.attname order by ordin) as key_col
from keys_unnest k inner join pg_attribute a_key on k.key_col_num = a_key.attnum and k.key_table_oid = a_key.attrelid and a_key.attisdropped = false
group by conname, key_schema, key_table, contype)
SELECT a.key_schema as schema, a.key_table AS table, a.contype AS superkey_type, a.conname as superkey_constraint_name, a.key_col AS superkey_col,
b.contype AS candidate_key_type, b.conname AS candidate_key_constraint_name, b.key_col AS candidate_key_col
FROM keys_with_names as a, keys_with_names as b
WHERE  a.key_schema=b.key_schema AND a.key_table=b.key_table AND
NOT (a.key_col@>b.key_col AND b.key_col@>a.key_col)
AND a.key_col@>b.key_col
ORDER BY  a.key_schema, a.conname, a.contype;