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

When looking at 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 way to print the name of Libya’s former dictator. According to this example there are around 30 ways to do it:

test=# CREATE TABLE t_code (name text);
CREATE TABLE
test=# Copy t_code FROM stdin;
Gadaffi
Gadafi
Gadafy
Gaddafi
Gaddafy
Gaddhafi
Gadhafi
Gathafi
Ghadaffi
Ghadafi
Ghaddafi
Ghaddafy
Gheddafi
Kadaffi
Kadafi
Kaddafi
Kadhafi
Kazzafi
Khadaffy
Khadafy
Khaddafi
Qadafi
Qaddafi
Qadhafi
Qadhdhafi
Qadthafi
Qathafi
Quathafi
Qudhafi
Kad'afi

Of course I could not resist to try it in PostgreSQL and see if things work just like 18 years ago. 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 got 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:

	test=# INSERT INTO t_code VALUES ('hans');
	INSERT 0 1

Let us give it a try now:

test=# SELECT name, name ~ '(Kh?|Gh?|Qu?)[aeu](d[''dt]?|t|zz|dhd)h?aff?[iy]' FROM t_code;
   name    | ?column?
-----------+----------
 Gadaffi   | t
 Gadafi    | t
...
 Qudhafi   | t
 Kad'afi   | t
 hans      | f
(31 rows)

WOW 🙂 Things work just like 20 years ago – without any changes, without a single problem. I guess this can be called a “safe investment”. Coding, which is working for 20+ years without any changes, can be considered as good code …