Find columns with JSON, JSONB, or XML type. Each columns should have the most appropriate data type.
Notes
The query finds information about base tables (including foreign tables), views, and materialized views. One has to find information about materialized views directly from the system catalog base tables because materialized views are not standardized in the SQL standard (as of SQL:2016) and the information is not presented through the INFORMATION_SCHEMA views. Query returns also information about columns that properties have been specified through a domain.
Type
General (Overview of some aspect of the database.)
WITH cols AS (SELECT table_schema, table_name, table_type, column_name, data_type
FROM INFORMATION_SCHEMA.columns INNER JOIN INFORMATION_SCHEMA.tables USING (table_schema, table_name)
WHERE table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT n.nspname AS table_schema, c.relname AS table_name, 'MATERIALIZED VIEW' AS table_type, at.attname AS column_name,
CASE WHEN t.typtype='d' THEN bt.typname ELSE t.typname 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='m'
AND at.attisdropped = false
AND at.attnum>=1)
SELECT table_schema, table_name, table_type, column_name, data_type
FROM cols
WHERE data_type~*'(xml|json)'
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.
Hierarchical data
Queries of this catergory provide information about storing hierarchical data in the database.