Only one value permitted in a base table or a foreign table column (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),

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

SELECT table_schema, table_name, table_type, column_name, co.type_schema AS column_type_schema, typname AS column_type_name, c.is_nullable
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)
ORDER BY table_schema, table_name;