Even ILIKE can use indexes, but only up to the first character where UPPER(x) != x and is not a wildcard, e.g. for numbers. The wildcard problem remains for plain LIKE as well. Examples:
(1) WHERE col ILIKE '123A'; -- will use index for the "123" prefix
(2) WHERE col ILIKE 'ABCD'; -- cannot use any b-tree index
(3) WHERE upper(col) LIKE upper('123A'); -- will use index on UPPER(col) for the entire value
(4) WHERE upper(col) LIKE upper('123%'); -- will use index for the "123" prefix
(5) WHERE upper(col) LIKE upper('%123'); -- cannot use any b-tree index
Cases 2 and 5 could work with some fulltext indexes, case 2 for sure, case 5 maybe (i seen this work in lucene)
Comment
Even ILIKE can use indexes, but only up to the first character where UPPER(x) != x and is not a wildcard, e.g. for numbers. The wildcard problem remains for plain LIKE as well. Examples:
(1) WHERE col ILIKE '123A'; -- will use index for the "123" prefix
(2) WHERE col ILIKE 'ABCD'; -- cannot use any b-tree index
(3) WHERE upper(col) LIKE upper('123A'); -- will use index on UPPER(col) for the entire value
(4) WHERE upper(col) LIKE upper('123%'); -- will use index for the "123" prefix
(5) WHERE upper(col) LIKE upper('%123'); -- cannot use any b-tree index
Cases 2 and 5 could work with some fulltext indexes, case 2 for sure, case 5 maybe (i seen this work in lucene)