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

Add new comment

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

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.