Tomas Berndtsson <[EMAIL PROTECTED]> 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
