Is order preserved after UNION in PostgreSQL?
Ordering is never preserved after any operation in any reasonable database. If you want the result set in a particular order, use ORDER BY
. Period.
This is especially true after a UNION
. UNION
removes duplicates and that operation is going to change the ordering of the rows, in all likelihood.
Basically, your query is incorrect to begin with. Use UNION ALL
, not or you would incorrectly remove duplicate entries. (There is nothing to say the trail cannot switch back and forth between the same emails.)UNION
The Postgres implementation for UNION ALL
returns values in the sequence as appended - as long as you do not add ORDER BY
at the end or do anything else with the result.
Be aware though, that each SELECT
returns rows in arbitrary order unless ORDER BY
is appended. There is no natural order in tables.
The same is not true for UNION
, which has to process all rows to remove possible duplicates. There are various ways to determine duplicates, the resulting order of rows depends on the chosen algorithm and is implementation-dependent and completely unreliable - unless, again, ORDER BY
is appended.
So use instead:
SELECT * FROM iter1
UNION ALL -- union all!
SELECT * FROM iter2;
To get a reliable sort order, and "simulate the record of growth", you can track levels like this:
WITH RECURSIVE all_emails AS (
SELECT *, 1 AS lvl
FROM audit_trail
WHERE old_email = '[email protected]'
UNION ALL -- union all!
SELECT t.*, a.lvl + 1
FROM all_emails a
JOIN audit_trail t ON t.old_email = a.new_email
)
TABLE all_emails
ORDER BY lvl;
db<>fiddle here
Old sqlfiddle
Aside: if old_email
is not defined UNIQUE
in some way, you can get multiple trails. You would need a unique column (or combination of columns) to keep it unambiguous. If all else fails you can (ab-)use the internal tuple ID ctid
for the purpose of telling trails apart. But you should rather use your own columns. (Added example in the fiddle.)
- In-order sequence generation
Consider:
- How to return records in correct order in PostgreSQL