Re: ILIKE

Peter Eisentraut <peter_e@gmx.net> writes:
> Josh Berkus writes:
>> 4) It's just as indexible (or not indexable) as regexp comparisons, and easier
>> to understand for users from the Microsoft world than regexp.

> ILIKE is not indexible at all.

You are arguing from a false premise.

regression=# create table foo (f1 text unique);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'foo_f1_key' for table 'foo'
CREATE TABLE
regression=# explain select * from foo where f1 ilike '123%';                              QUERY PLAN
------------------------------------------------------------------------Index Scan using foo_f1_key on foo
(cost=0.00..17.07rows=5 width=32)  Index Cond: ((f1 >= '123'::text) AND (f1 < '124'::text))  Filter: (f1 ~~*
'123%'::text)
(3 rows)

ILIKE is exactly as indexable as any other pattern that does the same
thing.
        regards, tom lane