Re: Simple Question: Case sensitivity - Mailing list pgsql-general

From Tom Lane
Subject Re: Simple Question: Case sensitivity
Date
Msg-id 7180.976548114@sss.pgh.pa.us
Whole thread Raw
In response to Re: Simple Question: Case sensitivity  (Tomas Berndtsson <tomas@nocrew.org>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Michael Ansley
Date:
Subject: RE: Regular expression question
Next
From: Tomas Berndtsson
Date:
Subject: Re: Simple Question: Case sensitivity