Re: case-insensitive Index

kmh496 <kmh496@kornet.net> writes:
> I tried declaring a varchar_pattern_ops and text_varchar_ops on a
> character varying (60) column but it still does a sequential scan on the
> column.

That's because the index doesn't match the query:

> mod=# create index english_english_word_idx on
> english_english(UPPER(word) text_pattern_ops)

> mod=# explain select word from english_english where word like 'here';

You'd need
    select word from english_english where upper(word) like upper('here');
to use that index (and also to get the right answer, if your goal is
case-insensitive matching).

            regards, tom lane