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.
Category “Yesql” — 42 articles
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.
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
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.
In this article we’ll play with the Magic: the Gathering card data in JSON format data set, provided with a CC0 licence, and process the information provided. We also see how to normalize a JSON document into a proper database model that benefits from some PostgreSQL advanced features, and how to then inject the JSON documents into the normalized database schema. Finally, we compare some non-trivial processing done against both versions of the database schema.
There’s a very rich set of PostgreSQL functions to process text, you can find them all at the String Functions and Operators documentation chapter, with functions such as overlay, substring, position or trim. Or aggregates such as string_agg. And then regular expression functions, including the very powerful regexp_split_to_table.
In a previous article here we saw How to Write SQL in your application code. The main idea in that article is to maintain your queries in separate SQL files, where it’s easier to maintain them. In particular if you want to be able to test them again in production, and when you have to work and rewrite queries.
The reason why I like Unicode a lot is because it allows me to code in text based environments and still have nice output. Today, we’re going to play with Regional Indicator Symbol, which is implemented as a Unicode combinaison of letters from 🇦 to 🇿. For instance, if you display 🇫 then 🇷 concatenated together, you get 🇫🇷. Let’s try that from our PostgreSQL prompt!