WITH one_value AS (SELECT nspname AS type_schema, typname
FROM pg_catalog.pg_type t LEFT JOIN pg_catalog.pg_enum e ON t.oid=e.enumtypid
INNER JOIN pg_catalog.pg_namespace n ON n.oid=t.typnamespace
WHERE nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) AND 
t.typtype='e'
GROUP BY nspname, typname
HAVING Count(enumtypid)=1),

cols as (select 
(select nspname from pg_namespace where oid=c.relnamespace) as table_schema,
c.relname as table_name, 
a.attname AS column_name,
n.nspname AS type_schema,
t.typname AS column_type,
CASE WHEN c.relkind ='r' THEN 'BASE TABLE' ELSE 'FOREIGN TABLE' END AS table_type
from pg_class c inner join pg_attribute a ON a.attrelid = c.oid AND a.attisdropped = FALSE
inner join pg_type AS t ON a.atttypid=t.oid
inner join pg_namespace as n ON t.typnamespace =n.oid
where c.relkind in ('r','f')),

cols_with_one_value AS (SELECT table_schema, table_name, table_type, ARRAY[column_name] AS column_name, co.type_schema, co.column_type
FROM cols AS co INNER JOIN one_value AS o ON co.type_schema=o.type_schema AND co.column_type=o.typname
INNER JOIN INFORMATION_SCHEMA.columns AS c USING (table_schema, table_name, column_name)),

keys as (select 
o.conname, 
(select nspname from pg_namespace where oid=c.relnamespace) 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
where 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 AS superkey_constraint, key_schema, key_table, table_type, contype, key_col, o.column_name AS column_that_permits_one_value, o.type_schema, o.column_type
FROM keys_with_names AS k INNER JOIN cols_with_one_value AS o 
ON k.key_schema=o.table_schema 
AND k.key_table=o.table_name 
AND cardinality(k.key_col) >cardinality(o.column_name)
AND k.key_col @>o.column_name 
ORDER BY key_schema, key_table, contype, key_col;