Function Upper or Lower is used in an index on a non-textual column

Back to Catalog
Goal Find function-based indexes that are based on function Upper or Lower but have been defined on a non-textual column. Such indexes support case insensitive search but in case of non-textual columns this does not have a meaning.
Notes The query also finds such indexes on a column that has case-insensitive character string type, citext. The query considers a possibility that the type of the column may be specified through a domain.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability High (Few or no false-positive results)
License MIT License
Fixing Suggestion Drop the indexes.
Data Source system catalog only
SQL Query 
WITH columns AS (SELECT 
n.nspname AS table_schema,
c.relname AS table_name,
at.attname AS column_name,
coalesce(dt.typname, t.typname) AS data_type
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 coalesce(dt.typname, t.typname)<>'text' AND coalesce(dt.typname, t.typname) NOT LIKE '%char%'
AND c.relkind='r' 
AND at.attisdropped='f'
AND at.attnum>0)
SELECT table_schema, table_name, column_name, data_type, p.indexname as case_insensitive_index_name, p.indexdef as case_insensitive_index_definition 
FROM columns c, pg_catalog.pg_indexes p
WHERE c.table_schema=p.schemaname AND c.table_name=p.tablename AND (p.indexdef LIKE '%(upper(%' || c.column_name || ')%' OR p.indexdef LIKE '%(lower(%' || c.column_name || ')%')
ORDER BY table_schema, table_name, column_name;

SQL statements that help generate fixes for the identified problem.

SQL Query to Generate FixDescription
WITH columns AS (SELECT 
n.nspname AS table_schema,
c.relname AS table_name,
at.attname AS column_name,
coalesce(dt.typname, t.typname) AS data_type
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 coalesce(dt.typname, t.typname)<>'text' and coalesce(dt.typname, t.typname) not like '%char%'
AND c.relkind='r' 
AND at.attisdropped='f'
AND at.attnum>0)
SELECT format('DROP INDEX %1$I.%2$I;', table_schema, p.indexname) AS statements
FROM columns c, pg_catalog.pg_indexes p
WHERE c.table_schema=p.schemaname AND c.table_name=p.tablename AND (p.indexdef LIKE '%(upper(%' || c.column_name || ')%' OR p.indexdef LIKE '%(lower(%' || c.column_name || ')%')
ORDER BY table_schema, table_name, p.indexname;
Drop the index.
Collections

This query belongs to the following collections:

NameDescription
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
Categories

This query is classified under the following categories:

NameDescription
Case sensitivity vs. case insensitivityQueries of this category provide information about case sensitivity/insensitivity of identifiers or user data.
Data typesQueries of this category provide information about the data types and their usage.
PerformanceQueries of this category provide information about indexes in a database.