Re: [PATCH] Add native PIVOT syntax for SQL Server/Oracle compatibility - Mailing list pgsql-hackers

From Myles Lewis
Subject Re: [PATCH] Add native PIVOT syntax for SQL Server/Oracle compatibility
Date
Msg-id BA031B3B-F27B-4B41-AED3-22E90CC508EE@sbcglobal.net
Whole thread Raw
In response to Re: [PATCH] Add native PIVOT syntax for SQL Server/Oracle compatibility  (Michael Paquier <michael@paquier.xyz>)
List pgsql-hackers
Hi Michael,

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

For reference, here's the vendor documentation:
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@paquier.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

Attachment

pgsql-hackers by date:

Previous
From: Nathan Bossart
Date:
Subject: Re: split tablecmds.c
Next
From: Nathan Bossart
Date:
Subject: Re: show size of DSAs and dshash tables in pg_dsm_registry_allocations