UNION, CASE, and related constructs-PolarDB(PolarDB)-阿里云帮助中心

UNION, INTERSECT, and EXCEPT constructs must resolve possibly dissimilar column types into a single result type. The resolution algorithm runs separately for each output column. The constructs CASE, ARRAY, VALUES, GREATEST, and LEAST use the same algorithm to select a result type from their component expressions.

How type resolution works

The result type is the highest-precedence type that all non-unknown inputs can be implicitly cast to. The following six rules determine that type, applied in order:

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 the domain's base type for all subsequent steps.
If all inputs are of type unknown, resolve as type text (the preferred type of the string category). Otherwise, ignore unknown inputs for 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. If the candidate type can be implicitly cast to the other type but not vice-versa, select the other type as the new candidate type. If a preferred type is selected at any stage, stop considering additional inputs.
Convert all inputs to the final candidate type. Fail if there is no implicit cast from a given input type to the candidate type.

Examples

Example 1: Underspecified types in a union

SELECT text 'a' AS "text" UNION SELECT 'b';

 text
------
 a
 b
(2 rows)

Rule 3 applies: the unknown-type literal 'b' resolves to type text.

Example 2: Simple union

SELECT 1.2 AS "numeric" UNION SELECT 1;

 numeric
---------
       1
     1.2
(2 rows)

Rule 5 applies: 1.2 is of type numeric. The integer value 1 can be implicitly cast to numeric, so numeric becomes the result type.

Example 3: Transposed union

SELECT 1 AS "real" UNION SELECT CAST('2.2' AS REAL);

 real
------
    1
  2.2
(2 rows)

Rule 5 applies: real cannot be implicitly cast to integer, but integer can be implicitly cast to real. The candidate type advances to real.

Example 4: Nested union (error)

SELECT NULL UNION SELECT NULL UNION SELECT 1;

ERROR:  UNION types text and integer cannot be matched

PostgreSQL evaluates multiple UNION clauses as nested pairwise operations, so the query above is equivalent to:

(SELECT NULL UNION SELECT NULL) UNION SELECT 1;

The inner UNION resolves to type text (Rule 3). The outer UNION then has inputs of types text and integer, which fail Rule 4. To fix this, make sure the leftmost UNION includes at least one input of the desired result type.

Usage notes

INTERSECT and EXCEPT: These constructs resolve types pairwise, the same as UNION. The other constructs covered in this topic (CASE, ARRAY, VALUES, GREATEST, LEAST) consider all their inputs in a single resolution step.
Domain types: A domain type is preserved through a UNION or similar construct only if all inputs are implicitly or explicitly of that exact domain type. Otherwise, the domain's base type is used.
CASE evaluation order: For historical reasons, CASE treats its ELSE clause (if any) as the first input and considers the THEN clause(s) afterward. In all other constructs, "left to right" means the order in which expressions appear in the query text.