New sql features

PIVOT and UNPIVOT Operators

In PostgreSQL, you can rotate a table using the CROSSTAB function. This function is passed a SQL query as a text parameter, which returns three columns:

• row ID – this column contains values identifying the resulting (rotated) row;
• category – unique values in this column determine the columns of the rotated table. Let’s note that, unlike PIVOT , these values themselves are of no significance; important is their number only, since it specifies the maximal possible number of columns;
• category value – the actual category values. The values are put in columns from left to right, and the category names play no role – just their order determined by the way the query is sorted is important.

We’ll explain this using the Painting database as an example.

Let’s sum up the amount of paint of each color for each square:

Console

Execute

SELECT b_q_id, v_color, SUM(b_vol) qty FROM utb JOIN utv ON b_v_id = v_id 
WHERE b_q_id BETWEEN 12 AND 16
GROUP BY b_q_id, v_color
ORDER BY b_q_id, CASE v_color WHEN 'R' THEN 1 WHEN 'G' THEN 2 ELSE 3 END;

select b_q_id, v_color, sum(b_vol) qty from utb join utv on b_v_id = v_id where b_q_id between 12 and 16 group by b_q_id, v_color order by b_q_id, case v_color when 'R' then 1 when 'G' then 2 else 3 end;

    Scheme

No scheme

Here, we confined ourselves to squares with IDs in the range between 12 to 16 in order to, on the one hand, keep the output small, yet, on the other hand, retain its representativeness. The colors are sorted in RGB order. Here is the result set:

In CROSSTAB terms, the spray can numbers represent row IDs, and the colors correspond to categories. The result of the table rotation should be as follows:

Now, let’s try to write a query using CROSSTAB that will return the desired result:

SELECT * FROM 
crosstab(
$$select b_q_id, v_color, SUM(b_vol) qty FROM utb JOIN utv ON b_v_id = v_id 
WHERE b_q_id BETWEEN 12 AND 16
GROUP BY b_q_id, v_color
ORDER BY b_q_id, CASE v_color WHEN 'R' THEN 1 WHEN 'G' THEN 2 ELSE 3 END; $$
) AS ct(square int, "R" bigint,"G" bigint,"B" bigint)
ORDER BY square;

Here, we have to list all columns specifying their types. Yet some of the category columns may be omitted. Let’s look at the result (you can check your queries using the console, and setting PostgreSQL for execution):

This result isn’t exactly what we expected. We recall that only the order of the categories matters. If the square has been painted with one color only, the value (total amount of paint) lands in the first category (named R in our query) no matter what color it actually was. Let’s rewrite our query to make it return values for all colors, and that in the order needed. Missing colors should be replaced by NULL. To achieve this, we’ll add for each square and color a row with a NULL in the paint amount field:

Console

Execute

SELECT b_q_id, v_color, SUM(b_vol) qty FROM(
SELECT b_q_id, v_color, b_vol FROM utb 
JOIN utv ON b_v_id = v_id 
UNION ALL --here addition is
SELECT * FROM (SELECT DISTINCT b_q_id FROM utb) X 
CROSS JOIN (SELECT 'R' color, NULL::smallint vol 
                UNION ALL SELECT 'G', NULL UNION ALL SELECT 'B', NULL) Y 
) X
WHERE b_q_id BETWEEN 12 AND 16
GROUP BY b_q_id, v_color
ORDER BY b_q_id, CASE v_color WHEN 'R' THEN 1 WHEN 'G' THEN 2 ELSE 3 END;

select b_q_id, v_color, sum(b_vol) qty from( select b_q_id, v_color, b_vol from utb join utv on b_v_id = v_id union all --here addition is select * from (select distinct b_q_id from utb) X cross join (select 'R' color, null::smallint vol                 union all select 'G', null union all select 'B', null) Y ) X where b_q_id between 12 and 16 group by b_q_id, v_color order by b_q_id, case v_color when 'R' then 1 when 'G' then 2 else 3 end;

    Scheme

No scheme

Now the query below will return the required result.

SELECT * FROM 
crosstab(
$$select b_q_id, v_color, SUM(b_vol) qty FROM(
SELECT b_q_id, v_color, b_vol FROM utb 
JOIN utv ON b_v_id = v_id 
UNION ALL
SELECT * FROM (SELECT DISTINCT b_q_id FROM utb) X 
CROSS JOIN (SELECT 'R' color, NULL::smallint vol 
               UNION ALL SELECT 'G', NULL UNION ALL SELECT 'B', NULL) Y 
) X
WHERE b_q_id BETWEEN 12 AND 16
GROUP BY b_q_id, v_color
ORDER BY b_q_id, CASE v_color WHEN 'R' THEN 1 WHEN 'G' THEN 2 ELSE 3 END;$$
) AS ct(square int, "R" bigint,"G" bigint,"B" bigint)
ORDER BY square;

You probably already start asking yourself whether there is some easier way to do that.

The answer is “yes”. As it turns out CROSSTAB can be passed another optional parameter – a query returning the category list in the same order used for the columns. Then, we can modify our first query as follows to make it yield the correct result:

SELECT * FROM 
crosstab(
$$select b_q_id, v_color, SUM(b_vol) qty FROM utb JOIN utv ON b_v_id = v_id 
WHERE b_q_id BETWEEN 12 AND 16
GROUP BY b_q_id, v_color
ORDER BY b_q_id, CASE v_color WHEN 'R' THEN 1 WHEN 'G' THEN 2 ELSE 3 END; $$,
$$select 'R' UNION ALL SELECT 'G' UNION ALL SELECT 'B';$$
) AS ct(square int, "R" bigint,"G" bigint,"B" bigint)
ORDER BY square;

Since PostgreSQL allows the usage of table value constructors, the query

SELECT 'R' UNION ALL SELECT 'G' UNION ALL SELECT 'B';

VALUES('R'),('G'),('B');

If, when running the query, you get an error message that crosstab is not recognized as a function, it means you don’t have the tablefunc module installed. This can be fixed by a simple command (starting with version 9.1)

CREATE EXTENSION IF NOT EXISTS tablefunc;