All key constraints

Copy SQL

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 conname, key_schema, key_table, contype, key_col
from keys_with_names
order by key_schema, key_table, contype, key_col;