At most one row is permitted in a table (based on enumeration types)

Copy SQL

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),

simple_keys as (select 
n_t.nspname 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_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_attribute a ON a.attrelid = c.oid AND a.attnum = o.conkey[1] 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
inner join pg_namespace n_t on c.relnamespace=n_t.oid
where o.contype in ('p','u')
and c.relkind in ('r','f')
and cardinality(o.conkey)=1
and n_t.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))

SELECT table_schema, table_name, table_type, column_name AS key_column_name, s.type_schema AS key_column_type_schema, typname AS key_column_type_name
FROM simple_keys AS s inner join one_value AS o ON s.type_schema=o.type_schema AND s.column_type=o.typname
AND EXISTS (SELECT *
FROM INFORMATION_SCHEMA.columns AS c
WHERE c.table_schema=s.table_schema
AND c.table_name=s.table_name
AND c.column_name=s.column_name
AND c.is_nullable='NO')
ORDER BY table_schema, table_name;