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

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

Andreas Schmid <user462411@gmail.com> writes:
> 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?

Yes, this is required by SQL spec.  Matching by column name would
be used if you wrote a CORRESPONDING clause, but we don't implement
that feature.

            regards, tom lane