Re: [PATCH] Add native PIVOT syntax for SQL Server/Oracle compatibility

Hi Michael,

Appreciate the feedback, the standards concern is fair and worth addressing.

For reference, here's the vendor documentation:
SQL Server (since 2005): https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot
Oracle (since 2007): https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6

You're right that PIVOT isn't in SQL 2023. That said, it's been in production across SQL Server and Oracle for close to 20 years now, and the syntax has become a de facto standard for this operation in other technologies. Row-to-column pivoting comes up frequently in reporting and analytics, right now PostgreSQL users likely perform similar types of operations using crosstab(), which requires knowing your columns ahead of time, or writing out verbose FILTER expressions by hand.

I do think there’s some precedent for PostgreSQL adopting useful non-standard syntax where it adds real value, RETURNING and ON CONFLICT come to mind.

That said, I'm genuinely curious: is there any discussion happening on the specification side around PIVOT or similar transformations? If there's emerging direction, I'd want to align with it. And if the feeling is that non-standard syntax doesn't belong in core, I'm open to packaging this as a contrib extension instead.

Either way, attached is an updated git patch with proper documentation, per your feedback.

Thanks,
Myles

> On Nov 26, 2025, at 6:35 PM, Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Tue, Nov 25, 2025 at 09:21:49PM +0100, Peter Eisentraut wrote:
>> Your patch does not contain any documentation, so it's hard to tell what
>> this is supposed to do if you don't already know those other products. Can
>> you supply at least some informal documentation, and maybe some links to
>> relevant documentation from those other products.
>
> (Added Vik Fearing in CC.)
>
> FWIW, I have very mixed feelings about the addition of clauses that
> are not part of the SQL specifications AFAIK (just looked at my copy
> of 2023), doing an implementation based on the argument of
> compatibility that have been taken by other products here, because we
> would have to live with this compatibility issues in the long-term.
>
> Peter, Vik, is there an equivalent under discussion on the
> specification side? If something gets released, an implementation may
> make sense based on it, but at this stage it would hard to decide why
> one direction would be more adapted than another.
> --
> Michael