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 (:variable, :'variable', and :"identifier").

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.


In our article Exploring a Data Set in SQL we discovered a data set related to music: the Chinook sample database.

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 mode() WITHIN GROUP (ORDER BY sort_expression) Ordered-Set Aggregate Function, as documented in the PostgreSQL page about Aggregate Functions.


Today is the day my book Mastering PostgreSQL in Application Development launches! I’m all excited that everybody interested is now able to actually read my book! Mastering PostgreSQL in Application Development targets application developers who want to learn SQL properly, and actually master this programming language. Most developers don’t think of SQL as a programming language, mainly because they don’t have full control of the execution plan of their queries.

PostgreSQL 10 is now available for everyone to use, and hinted by David Fetter I had to review my previous article on Json and SQL to adapt to Set Returning Functions changes.

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.


on Json and SQL

PostgreSQL has had proper json support for a while now. The unique extensibility approach of the PostgreSQL system allows it to enable native SQL friendly JSON processing.

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.

Dimitri Fontaine

PostgreSQL Major Contributor

Open Source Software Engineer

France