Goal Find base table columns with CITEXT type and make sure that case insensitivity is really needed in case of this column.
Notes The query takes into account a possibility that the type may be associated with the column through a domain.
Type General Overview of some aspect of the database.
License MIT (opens in new tab)
Data Source system catalog only
SQL Query
SELECT 
n.nspname AS table_schema,
c.relname AS table_name,
at.attname AS column_name
FROM pg_attribute at INNER JOIN pg_class c ON at.attrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_type AS t ON at.atttypid=t.oid
LEFT JOIN pg_type AS dt ON t.typbasetype=dt.oid
WHERE (nspname='public' OR rolname<>'postgres')
AND (t.typname='citext' OR dt.typname='citext')
AND c.relkind='r' 
AND at.attisdropped='f'
AND at.attnum>0
ORDER BY table_schema, table_name, column_name;

Collections

This query belongs to the following collections:

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 .

NameDescription
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

This query is classified under the following categories:

Data types

Queries of this category provide information about the data types and their usage.

NameDescription
Data typesQueries of this category provide information about the data types and their usage.