Postgres case insensitive searching

I started looking into making my “blog” software use case-insensitive searching. I thought that would make the “Search” facility for this site much more powerful.

The secret to Postgresql case-insensitive searching with SQL SELECT queries is to use regular expressions. If you’re used to standard Unix/POSIX regular expressions, the implementation is pretty easy as well. Instead of using the standard database LIKE operator, the solution is to use the Postgres ~* operator.

Postgres case-insensitive search example

Here’s a simple Postgresql case-insensitive search example:

SELECT subject FROM topics WHERE subject ~* 'eclipse';

This Postgres query returns all matches from the subject field in the topics table that contain the phrase 'eclipse', but because it is a case-insensitive search, it also matches things like eclipse and Eclipse and even EcLiPsE.

As a word of warning I don’t know how this works with indexes and table-scanning, so if you’re performing a query on a large database table you’ll want to be careful and test the performance of this approach.

In summary, if you’re looking for a way to perform a case-insensitive SQL SELECT query using Postgresql, I hope this is helpful.