public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: [email protected]
To: [email protected]
Subject: Re: UNION does not append
Date: Wed, 08 Oct 2025 10:33:31 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: UNION does not append
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox