Extending PostgreSQL for fun: with cats

Perhaps you’ve thought I wish I had more cats in my Postgres database before. We certainly have.

Just the other day we were lamenting some of the differences between MySQL and PostgreSQL, particularly the way that MySQL has case-insensitive matching using the LIKE operator, while Postgres has LIKE and ILIKE. This got us thinking, it’d be amusing to have more vague (and hilariously unwieldy) operators, such as:

SELECT * FROM foo WHERE a VAGUELY RESEMBLES b;

Something like this isn’t too implausible. It’s similar to what a full-text search entails, but it’s far from trivial. This got us thinking: if we can’t easily do that, can we at least have some amusing query syntax? Yes we can!

It turns out that a bare underscore is a valid field name in a table, so we created a dummy table and populated it with some Valuable Data:

Postgres’ rich extensibility options lets us define arbitrary functions, and also new symbol-y operators, meaning you can replicate PHP’s daft === operator (“really-really-really-equal, scout’s honour”) if you want to. We’re lucky that the characters we wanted to use were on the whitelist, so a few commands later…