Edd Mann Developer

Operations on a Deck of Cards in PostgreSQL

Recently I have been looking more into SQL and in-particular how RDMS’s work under-the-hood. I thought it would be interesting to use some of the lesser-known features that PostgreSQL has to offer, concentrating my efforts on operations performed on a deck of cards.

Using the 'IS (NOT) DISTINCT FROM' SQL comparators

In a recent SQL statement I stumbled upon an issue in-regard to handling NULL values within a given predicate. Coming from languages (such as PHP) which are very liberal in their type coercion (NULL can be treated as a falsey value), I was surprised to find how the inclusion of such a value resulted in a sort of predicate short-circuiting.

Map-Filter Combination in PHP

Following on from my discussion on Mapping, Filtering and Reducing in PHP, over the past couple of months I have been using a little trick I would like to discuss. Although a map-filter combination can be achieved in a single reduce, reading the PHP documentation I found out that array_filter supplied without a predicate function will remove all false values from the collection. This means that you can simply map over a collection and return false if the desired predicate does not match - leaving the filter to do the clean-up.

Maintaining Invariant Constraints in PostgreSQL using Trigger Functions

Recently a feature I was working on required me to alter a unique constraint which existed upon a table column. The invariant had now been weakened to allow storing of duplicate email addresses based on if they share an equivalent link_id (excluding NULL). Sadly the ease in which I was able to add the general unique constraint had disappear. However, I was able to take advantage of insertion/update triggers to again provide me with these invariant reassurances.