Postgresql regular expressions (syntax, operators)

Here’s a quick note about using Postgresql regular expressions and its regular expression syntax.

If you’re familiar with Unix and Perl regular expressions, these are very similar, so it’s an easy pickup, and a very powerful way to search for data in Postgres. Just use these regular expressions with SQL SELECT queries instead of the LIKE operator, and you’re ready to go.

Postgresql regex syntax

Here’s a little table of the Postgresql regular expression operators I have used:

Comparison Operator
regular expression ~
regular expression, case insensitive ~*
not equal to regular expression !~
not equal to regular expression, case insensitive !~*

As an example of how to use these regular expressions, here’s an example of a case-insensitive SQL SELECT query using Postgresql:

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