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