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 (opens in new tab)
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.

Fix Action #1
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.

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:

Find problems automatically

Queries, 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 .

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:

Case sensitivity vs. case insensitivity

Queries of this category provide information about case sensitivity/insensitivity of identifiers or user data.

Data types

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

Performance

Queries of this category provide information about indexes in a database.

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.