Category “Yesql” — 33 articles

In a previous article about Trigger Parameters we have been using the extension hstore in order to compute some extra field in our records, where the fields used both for the computation and for storing the results were passed in as dynamic parameters. Today we’re going to see another trigger use case for hstore: we are going to record changes made to our tuples.

Sometimes you want to compute values automatically at INSERT time, like for example a duration column out of a start and an end column, both timestamptz. It’s easy enough to do with a BEFORE TRIGGER on your table. What’s more complex is to come up with a parametrized spelling of the trigger, where you can attach the same stored procedure to any table even when the column names are different from one another.

There was SQL before window functions and SQL after window functions: that’s how powerful this tool is. Being that of a deal breaker unfortunately means that it can be quite hard to grasp the feature. This article aims at making it crystal clear so that you can begin using it today and are able to reason about it and recognize cases where you want to be using window functions.

We see a part of the data as if through a little window

In a recent article Craig Kerstiens from Heroku did demo the really useful crosstab extension. That function allows you to pivot a table so that you can see the data from different categories in separate columns in the same row rather than in separate rows. The article from Craig is Pivoting in Postgres. Pivoting a matrix, also known as a matrix transposition Let’s do the same setup as he did, with a table containing some randomly generated data about hypothetical visits to a web page, say, by date then by operating system.

In this article, we want to find the town with the greatest number of inhabitants near a given location. A very localized example We first need to find and import some data, and I found at the following place a CSV listing of french cities with coordinates and population and some numbers of interest for the exercise here. To import the data set, we first need a table, then a COPY command:

Dimitri Fontaine

PostgreSQL Major Contributor

Open Source Software Engineer