Thread: the current scoop on ilike and indexes

I am pretty sure the answer is no, but ... is there any way to get
'ilike' to use an index?  It seems like something that a lot of people
would want to do.  Otherwise, should I just create redundant
case-mapped columns and use 'like'?

Thanks,
Kevin Murphy

You can use an index on an expression like "lower( col ) LIKE ... " as
long as the LIKE expression is left-anchored. See

http://www.postgresql.org/docs/7.4/static/indexes-expressional.html

-tfo

On Sep 21, 2004, at 1:16 PM, Kevin Murphy wrote:

> I am pretty sure the answer is no, but ... is there any way to get
> 'ilike' to use an index?  It seems like something that a lot of people
> would want to do.  Otherwise, should I just create redundant
> case-mapped columns and use 'like'?
>
> Thanks,
> Kevin Murphy

On Sep 21, 2004, at 4:52 PM, Thomas F.O'Connell wrote:
> You can use an index on an expression like "lower( col ) LIKE ... " as
> long as the LIKE expression is left-anchored. See

Yes, I know that already.  I wasn't talking about LIKE; I was talking
about ILIKE.  The data in the column is mixed-case.

-Kevin

So the answer is that ILIKE will not use indexes.

But using lower()/LIKE will give you exactly the same results. lower()
forces all column data to lower case for the purposes of comparison.

-tfo

On Sep 21, 2004, at 4:07 PM, Kevin Murphy wrote:

> On Sep 21, 2004, at 4:52 PM, Thomas F.O'Connell wrote:
>> You can use an index on an expression like "lower( col ) LIKE ... "
>> as long as the LIKE expression is left-anchored. See
>
> Yes, I know that already.  I wasn't talking about LIKE; I was talking
> about ILIKE.  The data in the column is mixed-case.
>
> -Kevin
>