Re: ILIKE vs indices

On Fri, Dec 28, 2012 at 11:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> James Cloos <cloos(at)jhcloos(dot)com> writes:
>> Is there any contraindication to recasting:
>> foo ILIKE 'bar'
>> into:
>> LOWER(foo) LIKE LOWER('bar')
>
> In some locales those are not equivalent, I believe, or at least
> shouldn't be. (What the current code actually does is a separate
> question.)

What it actually does is actually *precisely* the above.

I can't quite wrap my head around the idea of "LIKE" and collations
having any meaningful interaction anyways. I certainly can't come up
with anything better than "lower() like lower()" (or "upper() like
upper()").

It would be nice to document what ILIKE actually means. Right now it's
kind of mysterious. And if we can't come up with anything better than
"lower() like lower()" then why not go ahead and document it and take
advantage of it.

--
greg