Re: [patch] A \pivot command for psql

From: "Daniel Verite" <daniel(at)manitou-mail(dot)org>
To: "David Fetter" <david(at)fetter(dot)org>
Cc: "Robert Haas" <robertmhaas(at)gmail(dot)com>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [patch] A \pivot command for psql
Date: 2015-08-13 13:44:07
Message-ID: cd521513-1349-4698-b93c-693199962e23@mm
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

David Fetter wrote:

> That said, a thing in psql that could slice serialized output into
> columns would be handy as a broad, general part of reporting in
> psql

To avoid any confusion with server-side PIVOT, I suggest that the
currently proposed command in psql should have a different name
than \pivot.

The general idea is indeed pivoting, but what it precisely does
is project a resultset from a 3-column query onto cells in a
2D grid-like arrangement.

It differs significantly from what existing PIVOT SQL commands
do, looking closely at them it appears in particular that:

- they don't care about a leftmost column to hold "row titles",
whereas it's essential to the psql feature.

- as mentioned upthread, the need to enumerate in advance the output
columns is a crucial point with the SQL pivot, whereas psql doesn't care,
as it just displays a resultset that is already obtained.

- they operate with an aggregate function at their heart, whereas it's
also irrelevant to the psql display feature whether there's an
aggregate in the query.

For a different name, I've thought of these alternatives that belong
to client-side vocabulary:

\grid3view (to insist that it works on 3 columns).
\crosstab (at the risk of confusion with the contrib feature)

Opinions? I'd go for \rotate personally.

Also I'll try to demonstrate use case with concrete examples.

Best regards,
Daniel Vérité
PostgreSQL-powered mailer:
Twitter: @DanielVerite

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2015-08-13 13:55:03 Re: Test code is worth the space
Previous Message David Steele 2015-08-13 13:32:02 Re: Test code is worth the space