On Thu, Jul 14, 2022 at 9:16 AM Shay Rojansky <r...@roji.org> wrote: > > >> I was trying to understand what - if any - are the guarantees with > regards to ordering for combining queries (UNION/UNION ALL/...). From this > message[1], it seems that UNION ALL does preserve the ordering of the > operand queries, whereas UNION does not (presumably neither do INTERSECT, > INTERSECT ALL, EXCEPT and EXCEPT ALL). > >> > >> The documentation[2] makes no mention of this, I'd suggest adding a > note clarifying this. > > > > If you want ordered output use ORDER BY. > > I don't see how that could be done. Consider the following: > > (SELECT id FROM data ORDER BY id) > UNION ALL > (SELECT id FROM data ORDER BY id DESC); > > If there's a guarantee that UNION ALL preserves ordering - as Tom seems to > indicate in the thread quoted above - then the above works. If there's no > such guarantee, then AFAIK the above can't be rewritten; putting the ORDER > BY outside - on the results of the UNION ALL - would order all results > rather than preserving each resultset's ordering. > > No, there is no guarantee. It's just that UNION ALL works this way today (preserving the order of the subselects) - and I'm not even sure about that, it may not preserve the order in all cases, with different indexes or partitioning or a parallel plan, etc. In any case, there is no guarantee that the behaviour will not change in the future due to planner improvements.
Best regards Pantelis Theodosiou > [1] https://www.postgresql.org/message-id/26825.1064858...@sss.pgh.pa.us > [2] https://www.postgresql.org/docs/current/queries-union.html > > > > > >
