with 
column_count as (select table_schema, table_name, count(*) as cnt
from information_schema.columns
where (table_schema, table_name) in
(select table_schema, table_name from information_schema.tables where table_type='BASE TABLE')
group by table_schema, table_name),

two_or_more_fk as (select c.relnamespace as target_schema_oid, c.oid as target_table_oid
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype = 'f'
group by target_schema_oid, target_table_oid
having count(*)>1),

fk as (select 
o.conname,
(select nspname from pg_namespace where oid=c.relnamespace) as target_schema,
c.relname as target_table, 
c.oid as target_table_oid,
unnest(o.conkey) AS target_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype = 'f' and
(c.relnamespace, c.oid) in (select target_schema_oid, target_table_oid from two_or_more_fk)),

fk_columns as (
select fk.conname, fk.target_schema, fk.target_table, a_target.attname as target_col
from fk inner join pg_attribute a_target on fk.target_col = a_target.attnum and fk.target_table_oid = a_target.attrelid and a_target.attisdropped = false),
fk_columns_grouped as (select target_schema, target_table, array_agg(distinct target_col order by target_col) as target_columns
from fk_columns
group by target_schema, target_table),

key as (select 
o.conname,
(select nspname from pg_namespace where oid=c.relnamespace) as target_schema,
c.relname as target_table, 
c.oid as target_table_oid,
unnest(o.conkey) AS target_col,
CASE WHEN contype='p' THEN 'PRIMARY KEY' ELSE 'UNIQUE' END AS contype
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('p','u')),

key_columns as (
select key.conname, key.contype, key.target_schema, key.target_table, a_target.attname as target_col
from key inner join pg_attribute a_target on key.target_col = a_target.attnum and key.target_table_oid = a_target.attrelid and a_target.attisdropped = false),

key_columns_grouped as (select target_schema, target_table, contype, array_agg(distinct target_col order by target_col) as target_columns
from key_columns
group by target_schema, target_table, contype)

select c.table_schema, c.table_name,  c.cnt as table_column_count, f.target_columns as fk_columns, p.contype as key_type, p.target_columns as key_columns
from column_count as c inner join fk_columns_grouped f on c.table_schema=f.target_schema and c.table_name=f.target_table
inner join key_columns_grouped p on c.table_schema=p.target_schema and c.table_name=p.target_table
where cardinality(f.target_columns)=cardinality(p.target_columns) and cardinality(f.target_columns)=c.cnt
order by table_schema, table_name;