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
constructs resolve dissimilar types in the same way as
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 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.