In PostgreSQL, string comparisons are case-sensitive by default. Making them case-insensitive requires the ILIKE operator and may require an additional index for performance reasons.
All the following operators are case-sensitive:
ILIKE operator is case-insensitive.
SELECT id FROM book WHERE title ILIKE 'alice%wonder%';
“Alice in Wonderland” has a capital A and a capital W, yet it will match this search.
ILIKE can also be used as a case-insensitive equality operator. You simply avoid using wildcard characters:
SELECT id FROM user WHERE name ILIKE 'john';
In PostgreSQL, the
ILIKE operator can’t use a regular btree index.
The solution that we recommend is to create a
gin or gist
index. Such indexes can be created as follows:
CREATE INDEX gin_title ON book USING gin(title); CREATE INDEX gist_title ON book USING gist(title);
The differences between
gist will be explained in a future article.
Some people recommend to create a btree index on an expression instead, though we find this solution less ideal:
CREATE INDEX lower_title ON book (LOWER(title)); SELECT id FROM book WHERE LOWER(title) LIKE 'alice%';