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. A CASE construct also uses the identical algorithm to match up its
component expressions and select a result data type.
UNION and CASE Type Resolution
-
If all inputs are of type unknown, resolve as type text (the preferred type for string category). Otherwise, ignore the
unknown inputs while choosing the 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 coerced to it.
-
Coerce all inputs to the selected type.
Example 7-7. Underspecified Types in a Union
tgl=> 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 7-8. Type Conversion in a Simple Union
tgl=> 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 7-9. Type Conversion in a Transposed Union
tgl=> SELECT 1 AS "Real"
tgl-> 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.
