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 Postgres case insensitive searching 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, you use the Postgres ~* operator.

Postgres case insensitive search example

Here's a simple Postgres case insensitive search example:

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

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

This whole topic is so cool I'm going to save the rest for a full-feature article, but for now, just know that you can do case-insensitive searching with Postgres very easily. For now here's a URL to some RedHat docs on the subject. When I write a good tutorial on this, I'll make sure I put another link here that leads to that tutorial.

Post new comment

The content of this field is kept private and will not be shown publicly.<