Re: Simple Question: Case sensitivity

Tomas Berndtsson <tomas@nocrew.org> writes:
> Related to this, is there any way to make an index for a table
> case-insensitive? If you have an index, but use upper() in the select,
> the index is not used.

Sure, make a functional index:

play=> create table foo (f1 text);
CREATE
play=> create index fooi on foo (upper(f1));
CREATE

This index will be considered for queries like:

play=> explain select * from foo where upper(f1) = 'z';
NOTICE:  QUERY PLAN:

Index Scan using fooi on foo  (cost=0.00..8.16 rows=10 width=12)

EXPLAIN
play=> explain select * from foo where upper(f1) > 'a' and upper(f1) < 'z';
NOTICE:  QUERY PLAN:

Index Scan using fooi on foo  (cost=0.00..8.21 rows=10 width=12)

EXPLAIN

You can use the same sort of ploy for lower() or any other simple
function of the table's columns.  Don't go overboard with a ton of
indexes though; remember each index costs time when updating the
table...

            regards, tom lane