The list of all the queries

Columns with JSON, JSONB, or XML type

Query goal: Find columns with JSON, JSONB, or XML type. Each columns should have the most appropriate data type.
Notes about the query: 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.
Query type: General (Overview of some aspect of the database.)
Query license: MIT License
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

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 where the query belongs to

Collection nameCollection description
Find problems by overviewQueries 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 where the query belongs to

Category nameCategory description
Data typesQueries of this category provide information about the data types and their usage.
Hierarchical dataQueries of this catergory provide information about storing hierarchical data in the database.

The list of all the queries