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)