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 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.
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
.