Category “PostgreSQL” — 131 articles

Simple Case for Pivoting in SQL

3 mins read PostgreSQL, YeSQL
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.

Nearest Big City

6 mins read PostgreSQL, YeSQL
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:

Bulk Replication

2 mins read PostgreSQL, Skytools
In the previous article here we talked about how to properly update more than one row at a time, under the title Batch Update. We did consider performances, including network round trips, and did look at the behavior of our results when used concurrently. A case where we want to apply the previous article approach is when replicating data with a trigger based solution, such as SkyTools and londiste. Well, maybe not in all cases, we need to have a amount of UPDATE trafic worthy of setting up the solution.

Batch Update

5 mins read PostgreSQL, YeSQL

Performance consulting involves some tricks that you have to teach over and over again. One of them is that SQL tends to be so much better at dealing with plenty of rows in a single statement when compared to running as many statements, each one against a single row.

HyperLogLog Unions

2 mins read PostgreSQL, YeSQL
In the article from yesterday we talked about PostgreSQL HyperLogLog with some details. The real magic of that extension has been skimmed over though, and needs another very small article all by itself, in case you missed it. *Which Set Operation do you want for counting unique values?* The first query here has the default level of magic in it, really. What happens is that each time we do an update of the HyperLogLog hash value, we update some data which are allowing us to compute its cardinality.

Dimitri Fontaine

PostgreSQL Major Contributor

Open Source Software Engineer

France