Re: Clarify the ordering guarantees in combining queries (or lack thereof)

>> 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.

[1] https://www.postgresql.org/message-id/26825.1064858799@sss.pgh.pa.us
[2] https://www.postgresql.org/docs/current/queries-union.html