Thread: index for ilike operation

I found that in Postresql 9.0.3 documentation:

It is also possible to use B-tree indexes for ILIKE and ~*, but only if the pattern starts with non-alphabetic characters, i.e., characters that are not affected by upper/lower case conversion.

Can anyone please tell me how to configure that?

On 02/07/11 9:07 PM, AI Rumman wrote:
> I found that in Postresql 9.0.3 documentation:
>
> /It is also possible to use B-tree indexes for ILIKE and ~*, but only
> if the pattern starts with non-alphabetic characters, i.e., characters
> that are not affected by upper/lower case conversion.
>
> /Can anyone please tell me how to configure that?/
> /

hmm?  nothing to configure.  its a special case thats really not very
useful.

     WHERE something ILIKE '432432$#@$#%'

would use a index on something, since there's no characters in there
subject to case shifting.

ditto...

     WHERE something ~* '^42432$@#$#@'       (where ^ anchors the string
to match starting from the first char)