Waiting for 9.6 – Support \crosstabview in psql

On 8th of April, Alvaro Herrera committed patch:

Support \crosstabview in psql
 
\crosstabview is a completely different way to display results from a
query: instead of a vertical display of rows, the data values are placed
in a grid where the column and row headers come from the data itself,
similar to a spreadsheet.
 
The sort order of the horizontal header can be specified by using
another column in the query, and the vertical header determines its
ordering from the order in which they appear in the query.
 
This only allows displaying a single value in each cell.  If more than
one value correspond to the same cell, an error is thrown.  Merging of
values can be done in the query itself, if necessary.  This may be
revisited in the future.
 
Author: Daniel Verité
<span class="signoff">Reviewed-by: Pavel Stehule, Dean Rasheed</span>

Ability to make “crosstab" queries is often requested. We do have tablefunc contrib module, which includes some crosstab functions, but it's clearly not enough.

So, this new patch looks like pretty cool thing – even though it's psql extension, and not something you can reach in plain sql.

What it does, and how to use it?

Let's first get some sample data:

$ CREATE TABLE test AS
SELECT
    ('{a,b,c,d,e,f}'::text[])[1 + FLOOR(random() * 6)] AS type_1,
    ('{m,n,o,p,q,r}'::text[])[1 + FLOOR(random() * 6)] AS type_2,
    random() * 1000 AS some_float
FROM generate_series(1,20000);

Data looks like this:

$ SELECT * FROM test LIMIT 10;
 type_1 | type_2 |    some_float    
--------+--------+------------------
 e      | p      | 3.07655474171042
 e      | n      | 560.803526081145
 e      | q      |  812.53616232425
 c      | p      | 518.018746282905
 c      | o      | 160.399601329118
 a      | o      | 867.646968457848
 d      | p      | 508.367411792278
 b      | p      | 984.146263916045
 b      | p      |  985.20147614181
 a      | o      | 922.880260273814
(10 ROWS)

Now, crosstab lets us rotate the table so that rows become columns. So, let's start with something to rotate:

$ SELECT type_1, type_2, COUNT(*) FROM test GROUP BY type_1, type_2 LIMIT 5;
 type_1 | type_2 | COUNT 
--------+--------+-------
 f      | q      |   596
 e      | n      |   581
 c      | o      |   592
 d      | o      |   572
 e      | r      |   546
(5 ROWS)

OK. and now for the crosstab magic:

$ SELECT type_1, type_2, COUNT(*) FROM test GROUP BY type_1, type_2 \crosstabview type_1 type_2 COUNT
 type_1 |  q  |  n  |  o  |  r  |  m  |  p  
--------+-----+-----+-----+-----+-----+-----
 f      | 596 | 544 | 553 | 563 | 532 | 549
 e      | 593 | 581 | 547 | 546 | 524 | 507
 c      | 575 | 542 | 592 | 526 | 555 | 597
 d      | 542 | 588 | 572 | 563 | 573 | 541
 b      | 544 | 546 | 534 | 552 | 542 | 570
 a      | 556 | 550 | 614 | 510 | 509 | 572
(6 ROWS)

Nice. We can, of course, have the rows in different order:

$ SELECT type_1, type_2, COUNT(*) FROM test GROUP BY type_1, type_2 ORDER BY type_1 \crosstabview type_1 type_2 COUNT
 type_1 |  r  |  p  |  m  |  n  |  q  |  o  
--------+-----+-----+-----+-----+-----+-----
 a      | 510 | 572 | 509 | 550 | 556 | 614
 b      | 552 | 570 | 542 | 546 | 544 | 534
 c      | 526 | 597 | 555 | 542 | 575 | 592
 d      | 563 | 541 | 573 | 588 | 542 | 572
 e      | 546 | 507 | 524 | 581 | 593 | 547
 f      | 563 | 549 | 532 | 544 | 596 | 553
(6 ROWS)

You can also reorder columns, using four arguments to \crosstabview:

$ SELECT type_1, type_2, COUNT(*) FROM test GROUP BY type_1, type_2 ORDER BY type_1 \crosstabview type_1 type_2 COUNT type_2
 type_1 |  m  |  n  |  o  |  p  |  q  |  r  
--------+-----+-----+-----+-----+-----+-----
 a      | 509 | 550 | 614 | 572 | 556 | 510
 b      | 542 | 546 | 534 | 570 | 544 | 552
 c      | 555 | 542 | 592 | 597 | 575 | 526
 d      | 573 | 588 | 572 | 541 | 542 | 563
 e      | 524 | 581 | 547 | 507 | 593 | 546
 f      | 532 | 544 | 553 | 549 | 596 | 563
(6 ROWS)

We can also, for example, pick some other column (from original query) to order columns in crosstab. For example, we might order it by count:

$ SELECT type_1, type_2, COUNT(*) FROM test GROUP BY type_1, type_2 ORDER BY type_1 \crosstabview type_1 type_2 COUNT COUNT
 type_1 |  m  |  r  |  n  |  q  |  p  |  o  
--------+-----+-----+-----+-----+-----+-----
 a      | 509 | 510 | 550 | 556 | 572 | 614
 b      | 542 | 552 | 546 | 544 | 570 | 534
 c      | 555 | 526 | 542 | 575 | 597 | 592
 d      | 573 | 563 | 588 | 542 | 541 | 572
 e      | 524 | 546 | 581 | 593 | 507 | 547
 f      | 532 | 563 | 544 | 596 | 549 | 553
(6 ROWS)

All in all it looks pretty interesting. I'm not sure if I will be using it, but there clearly is demand for pivoting of results, so I think it's a step in good direction. Thanks 🙂

2 thoughts on “Waiting for 9.6 – Support \crosstabview in psql”

  1. The sense of this feature is not clean on synthetic examples, but when you have real data then it has sense. Typical use case are reports where some aggregates are printed per month and per customer, region, cost type, ..

  2. Here is an example for pg_stat_statements, showing total_time summarized for each user/db pair:

    select
    (select usename from pg_user where usesysid = userid),
    (select datname from pg_database where oid = dbid),
    sum(total_time)
    from pg_stat_statements
    group by 1,2 order by 1,2;

    When many users and database are present, \crosstabview provides an output with much better readability

Comments are closed.