Regular expressions unleashed in PostgreSQL

When cleaning up some old paperwork this weekend I stumbled over a very old tutorial on regular expressions. In fact, I received this little handout during a UNIX course I attended voluntarily during my first year at university. It seems that those two days really changed my life - the price tag: 100 Austrian Shillings which translates to something like 7 Euros in today's money.

While looking over this old thing, I noticed a nice example showing how to test regular expression support in grep. Over the years I had almost forgotten this little test. Here is the idea: There is no single, unique way to transliterate the name of Libya's former dictator. According to this example there are around 30 ways to do it:

Of course I couldn't resist trying it in PostgreSQL to see if things work just like 18 years ago.

Regular Expressions in PostgreSQL

In PostgreSQL there are a handful of operators to work with regular expressions:

            ~          Matches regular expression, case-sensitive

            ~*        Matches regular expression, case-insensitive

            !~        Does not match regular expression, case-sensitive

            !~*      Does not match regular expression, case-insensitive

In our case, we have a case-sensitive regular expression, so that ~ operator should work. To make sure we get the right result, we will add one more row to the data set:

Let's give it a try now:

WOW 🙂 Things work just like 20 years ago - without any changes, without a single problem. I guess that can be called a “safe investment”. Coding which still works after 20+ years without any changes can be considered good code!

Read more about coding topics in this blog about case-insensitive pattern matching by Laurenz Albe.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.