PostgreSQL ships with an interactive console with the command line tool named psql. It can be used both for scripting and interactive usage and is moreover quite a powerful tool. Interactive features includes autocompletion, readline support (history searches, modern keyboard movements, etc), input and output redirection, formatted output, and more.
Florent Fourcot has read Mastering PostgreSQL in Application Development and has seen tremendous inprovements in his production setup from reading the first chapters and applying the book advices to his use case.
Here’s an interview run with Florent where he explains the context in which such improvements has been made!
The Enterprise Edition of Mastering PostgreSQL in Application Development ships with a docker image that hosts both a PostgreSQL server instance with a pre-loaded database, the one that’s used throughout the book examples, and also with a Jupyter Network notebook that hosts SQL queries thanks to the sql_magic plugin.
The PostgreSQL community made the explicit choice some times ago that they would not use the infamous master and slave terminology. Instead, the documentation introduces the concepts of High Availability, Load Balancing, and Replication with the terms Primary and Standby, and the even more generic term Replica is used in contexts when only the data flow is considered, rather than the particular role of a node.
In How to Write SQL we saw how to write
SQL queries as separate
.sql files, and we learnt about using query
parameters with the psql syntax for that (
For writing our database model, the same tooling is all we need. An important aspect of using psql is its capacity to provide immediate feedback, and we can also have that with modeling too.
Our discovery led us to find albums containing tracks of multiple genres, and for the analytics we were then pursuing, we wanted to clean the data set and assign a single genre per album. We did that in SQL of course, and didn’t actually edit the data.
Finding the most frequent input value in a group is a job for the
WITHIN GROUP (ORDER BY sort_expression) Ordered-Set Aggregate Function, as
documented in the PostgreSQL page about Aggregate
Now that my book Mastering PostgreSQL in Application Development is released (and selling well, thanks guys!), I’ve had some questions about the title.
The idea is that to become good at anything, we need to practice. We practice a lot, and it’s even better when we are actively trying to learn, following what’s named deliberate practice.
A Set Returning Function is a PostgreSQL Stored Procedure that can be used as a relation: from a single call it returns an entire result set, much like a subquery or a table.
It used to be possible to use SRF in the SELECT clause, with dubious (but useful at times) semantics, and also in scalar contexts. The semantics have been fixed and are now much clearer, and the uses in scalar contexts are forbidden — they were a hack and never made sense anyway.