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

Query 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 about the query:

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.

Query type:

Problem detection (Each row in the result could represent a flaw in the design)

Query reliability:

High (Few or no false-positive results)

Query license:

MIT License

Fixing suggestion:

Drop the indexes.

Data source:

system catalog only

SQL query:

Click on query to copy it

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 for generating SQL statements that help us to fix the problem

SQL query	Description
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

Description

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

Collection name	Collection description
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 .

Collection name

Collection description

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 .

Categories where the query belongs to

Category name	Category description
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.

Category name

Category description

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.