This query identifies columns defined with complex data types, specifically Arrays or User-Defined Types (UDTs). While PostgreSQL supports these advanced features, their indiscriminate use often signals an over-engineered schema. The query serves as a prompt to audit these columns and verify that the complex type is strictly necessary for performance or domain logic, and that a standard relational structure (e.g., scalar types or a child table) would not be a more appropriate and flexible design choice.
Type
General (Overview of some aspect of the database.)
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;
Collections
This query belongs to the following collections:
Name
Description
Find problems by overview
Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Categories
This query is classified under the following categories:
Name
Description
Data types
Queries of this category provide information about the data types and their usage.