Postgresql 中文操作指南

10.5. UNION, CASE, and Related Constructs #

SQL UNION 构造必须匹配可能的非相似类型才能成为单个结果集。解析算法应用于并集查询的每个输出列。INTERSECT 和 EXCEPT 构造以与 UNION 相同的方式解析非相似类型。包括 CASE、ARRAY、VALUES 及 GREATEST 和 LEAST 函数的一些其他构造使用相同的算法匹配其组分表达式并选择结果数据类型。

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. Some other constructs, including CASE, ARRAY, VALUES, and the GREATEST and LEAST functions, use the identical algorithm to match up their component expressions and select a result data type.

Type Resolution for UNION, CASE, and Related Constructs

后面有一些示例。

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)

此处，未知类型文本 'b' 将解析为 text 类型。

Here, the unknown-type literal 'b' will be resolved to type text.

Example 10.11. Type Resolution in a Simple Union

SELECT 1.2 AS "numeric" UNION SELECT 1;

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

文本 1.2 为 numeric 类型，并且 integer 值 1 可以隐式转换成 numeric，因此使用了该类型。

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.12. Type Resolution in a Transposed Union

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

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

此处，由于类型 real 无法隐式转换为 integer，但 integer 可以隐式转换为 real，因此联合结果类型解析为 real。

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.

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

此故障发生，是因为 PostgreSQL 将多个 UNION 视为成对操作的嵌套；即，此输入与

This failure occurs because PostgreSQL treats multiple _UNION_s as a nest of pairwise operations; that is, this input is the same as

(SELECT NULL UNION SELECT NULL) UNION SELECT 1;

内部 UNION 根据上述规则解析为发射类型 text。然后，外部 UNION 具有类型 text 和 integer 的输入，进而导致观察到的错误。可以通过确保最左侧的 UNION 至少具有一个所需的 result 类型输入来修复问题。

The inner UNION is resolved as emitting type text, according to the rules given above. Then the outer UNION has inputs of types text and integer, leading to the observed error. The problem can be fixed by ensuring that the leftmost UNION has at least one input of the desired result type.

INTERSECT 和 EXCEPT 操作同样成对解析。但是，本节中描述的其他构造在其一个解析步骤中考虑其所有输入。

INTERSECT and EXCEPT operations are likewise resolved pairwise. However, the other constructs described in this section consider all of their inputs in one resolution step.

[12 ] 有点类似于对运算符和函数的域输入的处理方式，只要用户小心确保所有输入隐式或显式地属于该确切类型，该行为就可以通过 UNION 或类似构造来保留一个域类型。否则，将使用域的基本类型。

[12] Somewhat like the treatment of domain inputs for operators and functions, this behavior allows a domain type to be preserved through a UNION 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 ] 由于历史原因， CASE 将其 ELSE 子句（如果任何）视为“第一个”输入，然后考虑 THEN 子句。在所有其他情况下，“从左到右”意味着表达式在查询文本中出现的顺序。

[13] For historical reasons, CASE treats its ELSE clause (if any) as the “first” input, with the THEN clauses(s) considered after that. In all other cases, “left to right” means the order in which the expressions appear in the query text.