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