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@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026