Re: UNION does not append

On Tue, 2025-10-07 at 22:27 +0000, PG Doc comments form wrote:
> Page: https://www.postgresql.org/docs/18/queries-union.html
>
> I'm very confused about this statement in the documentation of UNION: "UNION
> effectively appends the result of query2 to the result of query1".
> Because, if I append one item to a list, it is still the same list. But in
> the case of UNION, it's ""copied"" into a new anonymous derived-table. Alias
> don't work anymore, which I would expect if I just append some rows. Or is
> it like "effectively" means that this statement is not to be taken
> literally? In that case I would really appreciate a more detailed
> description, that after the UNION with tables a new anonymous derived-table
> is returned and the original first table can't be accessed anymore. Thank
> you for taking your time to read this even if you disagree.

I can understand your confusion. Yes, "effectively" means that you shouldn't
take it literally, but UNION does behave *as if* the second result is appended
to the first one.

Look at this:

SELECT * FROM (VALUES (1), (5), (3)) AS t1(x)
UNION ALL
SELECT * FROM (VALUES (2), (4), (3)) AS t2(y);

x
═══
1
5
3
2
4
3
(6 rows)

The column alias is "x", which is the alias from the first result.

And look at the execution plan:

EXPLAIN
SELECT * FROM (VALUES (1), (5), (3)) AS t1(x)
UNION ALL
SELECT * FROM (VALUES (2), (4), (3)) AS t2(y);

QUERY PLAN
═════════════════════════════════════════════════════════════════════
Append (cost=0.00..0.11 rows=6 width=4)
-> Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=4)
-> Values Scan on "*VALUES*_1" (cost=0.00..0.04 rows=3 width=4)
(3 rows)

So I'd say that the documentation is alright.

Yours,
Laurenz Albe