PostgreSQL: Making a comparison case-insensitive

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.

Case-sensitive comparisons

All the following operators are case-sensitive:

  • =
  • IN
  • LIKE

Case-insensitive searches

The 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';

Performance

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 gin and 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%';

References

Other articles:

PostgreSQL documentation: