public inbox for [email protected]help / color / mirror / Atom feed
UNION does not append 3+ messages / 3 participants [nested] [flat]
* UNION does not append @ 2025-10-07 22:27 PG Doc comments form <[email protected]> 0 siblings, 2 replies; 3+ messages in thread From: PG Doc comments form @ 2025-10-07 22:27 UTC (permalink / raw) To: [email protected]; +Cc: [email protected] 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UNION does not append @ 2025-10-08 08:33 Laurenz Albe <[email protected]> parent: PG Doc comments form <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Laurenz Albe @ 2025-10-08 08:33 UTC (permalink / raw) To: [email protected]; [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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UNION does not append @ 2025-10-08 11:29 David G. Johnston <[email protected]> parent: PG Doc comments form <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: David G. Johnston @ 2025-10-08 11:29 UTC (permalink / raw) To: [email protected] <[email protected]>; [email protected] <[email protected]> On Tuesday, October 7, 2025, PG Doc comments form <[email protected]> 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-10-08 11:29 UTC | newest] Thread overview: 3+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-10-07 22:27 UNION does not append PG Doc comments form <[email protected]> 2025-10-08 08:33 ` Laurenz Albe <[email protected]> 2025-10-08 11:29 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox