Re: Case insensitive selects?

David Wheeler <david@wheeler.net> writes:
>> Indexes *can* and *will* be used if you create the appropiate
>> functional indexes, i.e:
>>
>> CREATE INDEX idx_table_field_upper ON table(upper(field));
>>
>> SELECT field FROM table WHERE upper(field) LIKE upper('some string');

> Hmmm...I'd hate to have two indexes on every field I query like this, one
> case-senstive, one case-insensitve (like the one you create here). Is
> there a configuration option or something that will tell pgsql to do
> case-insensitive comparisons (kinda like MS SQL Server has)? That could
> save us on indexing overhead, since we want all of our WHERE comparisons
> to be case-insensitive, anyway.

Then why are you bothering to maintain a case-sensitive index?

There's no free lunch available here; if you think there is, then you
are misunderstanding what an index is.  Either the index is in
case-sensitive order, or it's not.

            regards, tom lane