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