10.5. UNION,CASE, and Related Constructs#
SQLUNION 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. TheINTERSECT andEXCEPT constructs resolve dissimilar types in the same way asUNION. Some other constructs, includingCASE,ARRAY,VALUES, and theGREATEST andLEAST functions, use the identical algorithm to match up their component expressions and select a result data type.
Type Resolution forUNION,CASE, and Related Constructs
If all inputs are of the same type, and it is not
unknown, resolve as that type.If any input is of a domain type, treat it as being of the domain's base type for all subsequent steps.[12]
If all inputs are of type
unknown, resolve as typetext(the preferred type of the string category). Otherwise,unknowninputs are ignored for the purposes of the remaining rules.If the non-unknown inputs are not all of the same type category, fail.
Select the first non-unknown input type as the candidate type, then consider each other non-unknown input type, left to right.[13] If the candidate type can be implicitly converted to the other type, but not vice-versa, select the other type as the new candidate type. Then continue considering the remaining inputs. If, at any stage of this process, a preferred type is selected, stop considering additional inputs.
Convert all inputs to the final candidate type. Fail if there is not an implicit conversion from a given input type to the candidate type.
Some examples follow.
Example 10.10. 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 to typetext.
Example 10.11. Type Resolution in a Simple Union
SELECT 1.2 AS "numeric" UNION SELECT 1; numeric--------- 1 1.2(2 rows)
The literal1.2 is of typenumeric, and theinteger value1 can be cast implicitly tonumeric, so that type is used.
Example 10.12. 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 typereal cannot be implicitly cast tointeger, butinteger can be implicitly cast toreal, the union result type is resolved asreal.
Example 10.13. Type Resolution in a Nested Union
SELECT NULL UNION SELECT NULL UNION SELECT 1;ERROR: UNION types text and integer cannot be matched
This failure occurs becausePostgreSQL treats multipleUNIONs as a nest of pairwise operations; that is, this input is the same as
(SELECT NULL UNION SELECT NULL) UNION SELECT 1;
The innerUNION is resolved as emitting typetext, according to the rules given above. Then the outerUNION has inputs of typestext andinteger, leading to the observed error. The problem can be fixed by ensuring that the leftmostUNION has at least one input of the desired result type.
INTERSECT andEXCEPT operations are likewise resolved pairwise. However, the other constructs described in this section consider all of their inputs in one resolution step.
[12] Somewhat like the treatment of domain inputs for operators and functions, this behavior allows a domain type to be preserved through aUNION or similar construct, so long as the user is careful to ensure that all inputs are implicitly or explicitly of that exact type. Otherwise the domain's base type will be used.
[13] For historical reasons,CASE treats itsELSE clause (if any) as the“first” input, with theTHEN clauses(s) considered after that. In all other cases,“left to right” means the order in which the expressions appear in the query text.