One of the friction points I have found in migrating from Oracle to PostgreSQL is in the conversion of hierarchical queries from the Oracle START WITH/CONNECT BY/ORDER SIBLINGS by pattern to using the ANSI recursive subquery form.
Once you wrap your head around it, the ANSI form is not so bad with one major exception. In order to achieve the equivalent of Oracle’s ORDER SIBLINGS BY clause, you need to add an additional column containing an array with the accumulated path back to the root of the hierarchy for each row. The problem with that is that it leaves you with an unfortunate choice: either accept the inefficiency of returning the array with the path back to the client (which the client doesn’t need or want), or requiring the application to explicitly list all of the columns that it wants just to exclude the hierarchy column, which can be hard to maintain, especially if your application needs to support both databases. If you have a ORM model where there could be multiple queries that share the same client code to read the result set, you might have to change multiple queries when new columns are added to a table or view even though you have centralized the processing of the result set.
The ideal solution for this would be for PostgreSQL to support the Oracle syntax and internally convert it to the ANSI form. Failing that, I have a modest suggestion that I would like to start a discussion around. What if you could use the MINUS keyword in the column list of a select statement to remove a column from the result set returned to the client? What I have in mind is something like this:
To achieve the equivalent of the following Oracle query:
SELECT T.*
FROM T
START WITH T.ParentID IS NULL
CONNECT BY T.ParentID = PRIOR T.ID
ORDER SIBLINGS BY T.OrderVal
You could use
WITH RECURSIVE TT AS (
SELECT T0.*, ARRAY[]::INTEGER[] || T.OrderVal AS Sortable
FROM T T0
UNION ALL
SELECT T1.*, TT.Sortable || T1 AS Sortable
FROM TT
INNER JOIN T T1 ON (T1.ParentID = TT.ID)
)
SELECT TT.* MINUS TT.Sortable
FROM TT
ORDER BY TT.Sortable
Now the Sortable column can be used to order the result set but is not returned to the client.
Not knowing the internals of the parser, I’m assuming that the use of MINUS in this construct would be distinguishable from the set difference use case because the expression being subtracted is a column (or perhaps even a lst of columns) rather than a SELECT expression.
