agora inbox for pgsql-docs@postgresql.org  

help / color / mirror / Atom feed

From: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: ruud@chorus.one
Subject: Clarification on the column order of UNION, INTERSECT, and EXCEPT
Date: Wed, 23 Jul 2025 16:29:16 +0000
Message-ID: <175328815637.785.8823107219955913007@wrigleys.postgresql.org> (raw)

The following documentation comment has been logged on the website:

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

When combining queries with UNION, INTERSECT, or EXCEPT, columns are matched
in the order in which they occur, and not by column name. This can be
unexpected, and I think it would be helpful to call it out explicitly in the
documentation.

Consider the following query:

    create table widgets (x text, y text);
    insert into widgets (x, y) values ('x', 'y');
    (select x, y from widgets) union all (select y, x from widgets);

On Postges 17.5, this returns:

    x y
    ─ ─
    x y
    y x

Note that it returns a value 'y' for column 'x', despite column x only
containing the value 'x'. This result makes sense if you think of rows as
anonymous tuples (ordered lists of values), but it can be surprising when
you expect rows to behave like structs/dictionaries with named fields.

This behavior can be especially tricky when columns x and y have the same
data type, such that confusing them does not trigger an error. In particular
when the values are similar (e.g. numeric data in the same range), it may
not be obvious from the result that data from different columns got mixed
up.

I read the documentation at

 - https://www.postgresql.org/docs/17/queries-union.html
 - https://www.postgresql.org/docs/17/typeconv-union-case.html
 - https://www.postgresql.org/docs/17/sql-select.html#SQL-UNION

These pages mention that the operands need to be compatible, e.g. ‘In order
to calculate the union, intersection, or difference of two queries, the two
queries must be “union compatible”, which means that they return the same
number of columns and the corresponding columns have compatible data types,
as described in Section 10.5.’ and ‘operands of the UNION must produce the
same number of columns, and corresponding columns must be of compatible data
types’. It does not explicitly state that “corresponding columns” means
“corresponding by index”, and not “corresponding by name”. I think it would
be helpful to clarify that, and maybe even warn about it.

Kind regards,
Ruud

view thread (15+ messages)  latest in thread

Message-ID: <175328815637.785.8823107219955913007@wrigleys.postgresql.org>
Permalink:  ../175328815637.785.8823107219955913007@wrigleys.postgresql.org/
Also on:    postgresql.org/message-id/175328815637.785.8823107219955913007@wrigleys.postgresql.org

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: pgsql-docs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, ruud@chorus.one
  Subject: Re: Clarification on the column order of UNION, INTERSECT, and EXCEPT
  In-Reply-To: <175328815637.785.8823107219955913007@wrigleys.postgresql.org>

* 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