On Sat, 22 Feb 2003, Peter Eisentraut wrote:
> AFAICT, ILIKE cannot use an index. So why does ILIKE even exist, when
> lower(expr) LIKE 'foo' provides a solution that can use an index and is
> more standard, too?
I would guess because for lower(expr) to work you need to make an index on
it. Since making ilike work invisibly would require the creation of an
"invisible" lower(expr) index, it would double index storage requirements
without warning the user.
To make ilike invisible it might be worth setting up a GUC that controls
automatic ilike index creation. That way ilike could either be a seq scan
all the time function, which is great for certain operations anyway, or
an automatically indexed operation.
#create_ilike_indexes = false # costs 2x storage on index of text, char,
types
I like ilike, but it's seq scan nature is a bit klunky.