10.5. UNION, CASE, and Related Constructs
SQL
UNION
constructs must match up possibly dissimilar
types to become a single result set. The resolution algorithm is
applied separately to each output column of a union query. The
INTERSECT
and
EXCEPT
constructs resolve
dissimilar types in the same way as
UNION
.
Some other constructs, including
CASE
,
ARRAY
,
VALUES
,
and the
GREATEST
and
LEAST
functions, use the identical
algorithm to match up their component expressions and select a result
data type.
Type Resolution for
UNION
,
CASE
,
and Related Constructs

If all inputs are of the same type, and it is not
unknown
, resolve as that type. 
If any input is of a domain type, treat it as being of the domain's base type for all subsequent steps. ^{ [12] }

If all inputs are of type
unknown
, resolve as typetext
(the preferred type of the string category). Otherwise,unknown
inputs are ignored for the purposes of the remaining rules. 
If the nonunknown inputs are not all of the same type category, fail.

Select the first nonunknown input type as the candidate type, then consider each other nonunknown input type, left to right. ^{ [13] } If the candidate type can be implicitly converted to the other type, but not viceversa, select the other type as the new candidate type. Then continue considering the remaining inputs. If, at any stage of this process, a preferred type is selected, stop considering additional inputs.

Convert all inputs to the final candidate type. Fail if there is not an implicit conversion from a given input type to the candidate type.
Some examples follow.
Example 10.10. Type Resolution with Underspecified Types in a Union
SELECT text 'a' AS "text" UNION SELECT 'b'; text  a b (2 rows)
Here, the unknowntype literal
'b'
will be resolved to type
text
.
Example 10.11. Type Resolution in a Simple Union
SELECT 1.2 AS "numeric" UNION SELECT 1; numeric  1 1.2 (2 rows)
The literal
1.2
is of type
numeric
,
and the
integer
value
1
can be cast implicitly to
numeric
, so that type is used.
Example 10.12. Type Resolution in a Transposed Union
SELECT 1 AS "real" UNION SELECT CAST('2.2' AS REAL); real  1 2.2 (2 rows)
Here, since type
real
cannot be implicitly cast to
integer
,
but
integer
can be implicitly cast to
real
, the union
result type is resolved as
real
.
Example 10.13. Type Resolution in a Nested Union
SELECT NULL UNION SELECT NULL UNION SELECT 1; ERROR: UNION types text and integer cannot be matched
This failure occurs because
PostgreSQL
treats
multiple
UNION
s as a nest of pairwise operations;
that is, this input is the same as
(SELECT NULL UNION SELECT NULL) UNION SELECT 1;
The inner
UNION
is resolved as emitting
type
text
, according to the rules given above. Then the
outer
UNION
has inputs of types
text
and
integer
, leading to the observed error. The problem
can be fixed by ensuring that the leftmost
UNION
has at least one input of the desired result type.
INTERSECT
and
EXCEPT
operations are
likewise resolved pairwise. However, the other constructs described in this
section consider all of their inputs in one resolution step.
^{
[12]
}
Somewhat like the treatment of domain inputs for operators and
functions, this behavior allows a domain type to be preserved through
a
UNION
or similar construct, so long as the user is
careful to ensure that all inputs are implicitly or explicitly of that
exact type. Otherwise the domain's base type will be used.
^{
[13]
}
For historical reasons,
CASE
treats
its
ELSE
clause (if any) as the
"
first
"
input, with the
THEN
clauses(s) considered after
that. In all other cases,
"
left to right
"
means the order
in which the expressions appear in the query text.