Goal This query identifies expression indexes that apply the upper() function to a column. In PostgreSQL, it is a strong community convention to standardize on the lower() function for case-insensitive indexing and comparisons. Enforcing this convention ensures consistency across the schema and prevents performance issues caused by queries failing to utilize indexes (for example, if a developer queries using lower(col) but the index is built on upper(col)).
Type Problem detection Each row in the result could represent a flaw in the design
Reliability Medium Medium number of false-positive results
License MIT (opens in new tab)
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
SELECT schemaname, tablename, indexname, indexdef
FROM pg_catalog.pg_indexes
WHERE indexdef LIKE 'CREATE UNIQUE INDEX%' AND 
indexdef LIKE '%(upper(%'
AND
schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
ORDER BY schemaname, tablename, indexname;

Fixing Suggestion

To resolve this issue and align with standard conventions, drop the existing expression index and recreate it using the lower() function instead of upper(). Additionally, ensure that all application queries relying on this column are updated to search using lower(col) so they continue to benefit from index acceleration.

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:

Inconsistencies

Queries of this catergory provide information about inconsistencies of solving the same problem in different places.

Performance

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

NameDescription
InconsistenciesQueries of this catergory provide information about inconsistencies of solving the same problem in different places.
PerformanceQueries of this category provide information about indexes in a database.