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

On Tuesday, October 7, 2025, PG Doc comments form <noreply@postgresql.org>
wrote:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/18/queries-union.html
Description:

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.

Yes, if you place a set-operation using query into a subquery context the
subquery has to be given its own name: and the individual from clause
relations of the contained query are not exposed. This is just how
subqueries work.

If you don’t place it in a subquery the rows are just sent to the client.

In both cases the word append simply means “using the column structure of
the first component query”. Though Imsuppose that is strictly “column
names” since types can be changed to a more encompassing one if needed.

David J.