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. The
CASE, ARRAY, VALUES,
GREATEST and LEAST constructs 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. Otherwise, replace any domain types in the list with
their underlying base types.
If all inputs are of type unknown, resolve as type
text (the preferred type of the string category).
Otherwise, the unknown inputs will be ignored.
If the non-unknown inputs are not all of the same type category, fail.
Choose the first non-unknown input type which is a preferred type in
that category, if there is one.
Otherwise, choose the last non-unknown input type that allows all the
preceding non-unknown inputs to be implicitly converted to it. (There
always is such a type, since at least the first type in the list must
satisfy this condition.)
Convert all inputs to the selected type. Fail if there is not a
conversion from a given input to the selected type.
Some examples follow.
Example 10-7. Type Resolution with Underspecified Types in a Union
SELECT text 'a' AS "text" UNION SELECT 'b';
text
------
a
b
(2 rows)
Here, the unknown-type literal 'b' will be resolved as type text.
Example 10-8. 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-9. 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.
