with def_columns AS (SELECT  table_schema,  table_name, c.column_name, c.is_identity, coalesce(column_default, domain_default) AS def
FROM INFORMATION_SCHEMA.columns AS c LEFT JOIN INFORMATION_SCHEMA.domains AS d ON c.domain_schema=d.domain_schema AND c.domain_name=d.domain_name),
def_columns_with_names AS (
SELECT table_schema, table_name, array_agg(column_name ORDER BY column_name)::text[] AS def_columns
FROM def_columns
WHERE def ILIKE '%nextval%' OR is_identity='YES'
GROUP BY table_schema, table_name),
keys 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,
o.conkey AS target_col,
case when o.contype='p' then 'PRIMARY KEY' ELSE 'UNIQUE' END as key_type
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('u', 'p') ),
keys_unnest as (select conname, target_schema, target_table, target_table_oid, target_col, target_col_num, ordin, key_type
from keys, unnest(keys. Target_col) with ordinality as f(target_col_num, ordin)),
keys_with_names as (select conname, target_schema, target_table, array_agg(a_target.attname order by a_target.attname)::text[] as key_columns, key_type
from keys_unnest k inner join  pg_attribute a_target on k.target_col_num = a_target.attnum and k.target_table_oid = a_target.attrelid and a_target.attisdropped = false
group by conname, target_schema, target_table, key_type)
SELECT K.target_schema, K.target_table, K.key_type, key_columns AS perhaps_superkey, def_columns AS perhaps_candidate_key 
FROM def_columns_with_names AS Dc, keys_with_names AS K
WHERE Dc.table_schema=K.target_schema AND Dc.table_name=K.target_table AND cardinality(key_columns)>cardinality(def_columns) AND key_columns@>def_columns
ORDER BY K.target_schema, K.target_table;