Columns with array or user-defined type

Copy SQL

WITH cols AS (SELECT n.nspname AS table_schema, c.relname AS table_name, 
CASE WHEN c.relkind='r' THEN 'BASE TABLE'
WHEN c.relkind='v' THEN 'VIEW'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW'
WHEN c.relkind='f' THEN 'FOREIGN TABLE'
WHEN c.relkind='p' THEN 'PARTITIONED TABLE' END AS table_type, at.attname AS column_name, 
CASE WHEN t.typtype='d' THEN 
CASE WHEN bt.typcategory='A' THEN translate(bt.typname,'_','') || '(ARRAY)' ELSE bt.typname END
ELSE 
CASE WHEN t.typcategory='A' THEN translate(t.typname,'_','') || '(ARRAY)' 
WHEN t.typcategory IN ('C') THEN t.typname || '(USER-DEFINED)'
ELSE t.typname END
END AS data_type
FROM pg_class AS c INNER JOIN pg_namespace AS n ON c.relnamespace=n.oid
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_attribute AS at ON c.oid=at.attrelid 
INNER JOIN pg_type AS t ON at.atttypid=t.oid
LEFT JOIN pg_type AS bt ON t.typbasetype=bt.oid
WHERE (n.nspname='public' OR rolname<>'postgres')
AND c.relkind IN ('r','v','m','f','p')
AND at.attisdropped = false
AND at.attnum>=1)
SELECT table_schema, table_name, table_type, column_name, data_type
FROM cols
WHERE data_type~*'(array|user-defined)'
ORDER BY table_type, table_schema, table_name;