Re: [PATCH] Add native PIVOT syntax for SQL Server/Oracle compatibility
| От | Myles Lewis |
|---|---|
| Тема | Re: [PATCH] Add native PIVOT syntax for SQL Server/Oracle compatibility |
| Дата | |
| Msg-id | BA031B3B-F27B-4B41-AED3-22E90CC508EE@sbcglobal.net обсуждение исходный текст |
| Ответ на | Re: [PATCH] Add native PIVOT syntax for SQL Server/Oracle compatibility (Michael Paquier <michael@paquier.xyz>) |
| Список | pgsql-hackers |
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@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
Вложения
В списке pgsql-hackers по дате отправления: