Psql query : Union is not preserving the order of rows coming from cte

I have this query:

with cte1 as (select salary from employees order by salary desc), cte2 as (select 850) select * from cte1 union select * from cte2;

cte1 is ordered by salary column but cte2 is not. I want the results of cte2 to get appended to the results of cte1 while preserving the order of results from cte1. But it’s not happening.

If I run the above query without union with the second cte, the results are displayed in the expected order but the order gets messed up when union is there.

Query without union:

with cte1 as (select salary from employees order by salary desc), cte2 as (select 850) select * from cte1;
 salary 
--------
   1000
    900
    800
    700
    600
    500

With union:

with cte1 as (select salary from employees order by salary desc), cte2 as (select 850) select * from cte1 union select * from cte2;
 salary 
--------
    850
    800
    700
    900
    500
    600
   1000

Can anyone please explain why is this happening?

Answer

The documentation is clear that union does not guarantees the ordering of the rows:

UNION effectively appends the result of query2 to the result of query1 (although there is no guarantee that this is the order in which the rows are actually returned).

If you want the results to be ordered, then use order by in the outer query; for your use case, this requires keeping track of which cte each row comes from

with 
    cte1 (salary, which) as (select salary, 1 from employees), 
    cte2 (salary, which) as (select 850, 2) 
select salary from cte1 
union all
select salary from cte2
order by which, salary desc;

Note that I changed union to union all; it does not seem like you want to deduplicates the rows (which the former does), so the latter is good enough (and more efficient).