Re: ILIKE

Peter Eisentraut <peter_e(at)gmx(dot)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.07 rows=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