UNION ALL: Apparently based on column order rather than on column name or alias

From: Andreas Schmid <user462411(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: UNION ALL: Apparently based on column order rather than on column name or alias
Date: 2018-12-06 14:48:02
Message-ID: CAKeZVDpNmfkQqkBT2x=zR=K58w1doHeCvJGQ84evAYNsKKXuZg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi list

I realized the following behaviour of UNION ALL:

SELECT 'a' AS col1, 'b' AS col2
UNION ALL
SELECT 'c' AS col1, 'd' AS col2;

returns:

col1 | col2
------+------
a | b
c | d

Now I switch the column aliases in the second SELECT-Statement:

SELECT 'a' AS col1, 'b' AS col2
UNION ALL
SELECT 'c' AS col2, 'd' AS col1;

This returns the same result:

col1 | col2
------+------
a | b
c | d

Same behaviour when working just with column names, no aliases.

So my conclusion is that the result of UNION ALL depends on the column
order, not on the column names or aliases. Is this the intended
behaviour? And is it documented somewhere? What I found is the last
sentence on https://www.postgresql.org/docs/current/queries-union.html
which says
"[...] they return the same number of columns and the corresponding
columns have compatible data types [...]"
It says nothing about column order, column names or aliases. Does this
obviously imply it's the column order?

Thank you for some clarification.
Andy

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephen Frost 2018-12-06 14:52:30 Re: order of reading the conf files
Previous Message Rene Romero Benavides 2018-12-06 14:46:21 Re: order of reading the conf files