Re: Case insensitive selects?

> On Thu, 15 Feb 2001, Michael Fork wrote:
>
> > 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.

I was wondering if we could do case-insensitive index waking by doing
looking for CAR as:

CAR
CAr
CaR
Car
cAR
cAr
caR
car

Basically you look for CAR, then back up in the btree, to CA and look
for r instead of R. I relized the number of tests would exponentially
explode, but isn't it just like btree walking where we back up to test
the lowercase of the letter.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026