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
LEAST constructs use the identical algorithm to
match up their component expressions and select a result data
Type Resolution for UNION, CASE, and Related Constructs
If all inputs are of type unknown, resolve as type text (the preferred type of the string category). Otherwise, ignore the unknown inputs while choosing the result type.
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 or allows all the non-unknown inputs to be implicitly converted to it.
Convert all inputs 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.