Static and dynamic pivots

How to do a dynamic pivot

So the difficulty of a dynamic pivot is: in an SQL query, the output columns must be determined before execution. But to know which columns are formed from transposing rows, we’d to need to execute the query first. To solve this chicken and egg problem, we need to loosen the constraints a bit.

Result in a nested structure inside a column

Firstly, a SQL query can return the pivoted part encapsulated inside a single column with a composite or array type, rather than as multiple columns. This type could be array[text], JSON, XML… This solution is used by Oracle with its PIVOT XML clause. That’s a one-step method, which is good, but the result has a non-tabular structure that does necessarily match the users expectations.

Here is an example using modern PostgreSQL with JSON:

SELECT city,
       json_object_agg(year,total ORDER BY year)
   FROM (
     SELECT city, year, SUM(raindays) AS total
        FROM rainfall
        GROUP BY city,year
   ) s
  GROUP BY city
  ORDER BY city;

Without having to enumerate the years in the query, we get the same data as above, but as 2 columns, the first for the “vertical axis”, and the next one for all the rest in JSON format:

   city    |                                    json_object_agg                                     
-----------+----------------------------------------------------------------------------------------
 Ajaccio   | { "2012" : 69, "2013" : 91, "2014" : 78, "2015" : 48, "2016" : 81, "2017" : 51 }
 Bordeaux  | { "2012" : 116, "2013" : 138, "2014" : 137, "2015" : 101, "2016" : 117, "2017" : 110 }
 Brest     | { "2012" : 178, "2013" : 161, "2014" : 180, "2015" : 160, "2016" : 165, "2017" : 144 }
 Dijon     | { "2012" : 114, "2013" : 124, "2014" : 116, "2015" : 93, "2016" : 116, "2017" : 103 }
 Lille     | { "2012" : 153, "2013" : 120, "2014" : 136, "2015" : 128, "2016" : 138, "2017" : 113 }
 Lyon      | { "2012" : 112, "2013" : 116, "2014" : 111, "2015" : 80, "2016" : 110, "2017" : 102 }
 Marseille | { "2012" : 47, "2013" : 63, "2014" : 68, "2015" : 53, "2016" : 54, "2017" : 43 }
 Metz      | { "2012" : 98, "2013" : 120, "2014" : 110, "2015" : 93, "2016" : 122, "2017" : 115 }
 Nantes    | { "2012" : 124, "2013" : 132, "2014" : 142, "2015" : 111, "2016" : 106, "2017" : 110 }
 Nice      | { "2012" : 53, "2013" : 77, "2014" : 78, "2015" : 50, "2016" : 52, "2017" : 43 }
 Paris     | { "2012" : 114, "2013" : 111, "2014" : 113, "2015" : 85, "2016" : 120, "2017" : 110 }
 Perpignan | { "2012" : 48, "2013" : 56, "2014" : 54, "2015" : 48, "2016" : 69, "2017" : 48 }
 Toulouse  | { "2012" : 86, "2013" : 116, "2014" : 111, "2015" : 83, "2016" : 102, "2017" : 89 }
(13 rows)

That somehow does the job, but visually the alignment is not very good, and if we want to copy-paste this result into a spreadsheet or import it as tabular data, it’s clear that it’s not going to work.

Getting a tabular result in two steps

The other solutions based on a SQL query are based on the idea of a two-step process:

  1. a first query build the result with all its columns, and returns an indirect reference to this result.
  2. a second query returns the result set, now that its structure is known by the SQL engine thanks to the previous step.

Ultimate PostgreSQL Slug Function

What is a slug?!

If you’re reading this, you probably know, but a slug is a URL/SEO friendly representation of a string. If for example you wrote an article:

The World’s “Best” Cafés!

You’d want a slug function to create a representation that looks a bit like:

the-worlds-best-cafes

Granted browsers can probably handle the accented “e” but it’s good to be over conservative for backwards compatibility.

PostgreSQL slug function

The most comprehensive slug function I’ve seen for Postgres (and in general) is by ianks on Github. Slug functions are one of those things that never seems to have a definitive “best and final” version, but this has been very reliable for me.

Code of Conduct Committee: 2018 Annual Report

The PostgreSQL Community Code of Conduct Committee was formally established by the PostgreSQL Core Team on September 14, 2018. The Committee members selected by the Core Team are:

  • Stacey Haysler, Chair
  • Laetitia Avrot
  • Ilya Kosmodemiansky
  • Jonathan Katz
  • Vik Fearing

In the period of September 14, 2018 through December 31, 2018, the Committee received the following reports:

  • Sexual harassment: 2
  • Threats of violence: 1

The results of the investigations:

  • Education and coaching: 1
  • Permanent ban from community resources: 1
  • Investigation ongoing as of December 31, 2018: 1

We would like to thank the Core Team and the community members who have supported the adoption of the Code of Conduct, and who continue to uphold the professional standards of the PostgreSQL Community.

psql2csv

Run a query in psql and output the result as CSV.

$ psql2csv dbname “select * from table” > data.csv

$ psql2csv dbname < query.sql > data.csv

$ psql2csv –no-header –delimiter=$’\t’ –encoding=latin1 dbname < SELECT *
> FROM some_table
> WHERE some_condition
> LIMIT 10
> sql