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.
The first task we need to complete is to create a table of the 52 cards that are present in a deck.
We can do this rather succinctly by taking advantage of PostgreSQL’s arrays and a cross-join (Cartesian product) between the ranks and suits.
Using a CTE by way of the
WITH clause, we are able to use this table in later queries by name.
We can then use this deck to retrieve a randomised five card hand.
Alternatively we can return all the possible combinations of hands present within a deck. Using a similar cross-join present when creating the deck we instead condition the result to only return unique combinations. This uses the fact that although each card is a string value, combined they are comparable and still provide a total order.