Message-ID: <4e024d5468aef77d168d46d86554fdabf802b031.camel@cybertec.at>
Subject: Re: UNION does not append
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: trescher40@gmx.de, pgsql-docs@lists.postgresql.org
Date: Wed, 08 Oct 2025 10:33:31 +0200
In-Reply-To: <175987603436.768.10549246436481476867@wrigleys.postgresql.org>
References: <175987603436.768.10549246436481476867@wrigleys.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/4e024d5468aef77d168d46d86554fdabf802b031.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-10-07 at 22:27 +0000, PG Doc comments form wrote:
> Page: https://www.postgresql.org/docs/18/queries-union.html
>=20
> I'm very confused about this statement in the documentation of UNION: "UN=
ION
> 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 i=
n
> the case of UNION, it's ""copied"" into a new anonymous derived-table. Al=
ias
> don't work anymore, which I would expect if I just append some rows. Or i=
s
> 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-tab=
le
> 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 appen=
ded
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=20
  =E2=95=90=E2=95=90=E2=95=90
   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                             =20
  =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90
   Append  (cost=3D0.00..0.11 rows=3D6 width=3D4)
     ->  Values Scan on "*VALUES*"  (cost=3D0.00..0.04 rows=3D3 width=3D4)
     ->  Values Scan on "*VALUES*_1"  (cost=3D0.00..0.04 rows=3D3 width=3D4=
)
  (3 rows)

So I'd say that the documentation is alright.

Yours,
Laurenz Albe