Postgresql 中文操作指南
9.18. Conditional Expressions #
此部分介绍 PostgreSQL 中可用的 SQL 兼容条件表达式。
This section describes the SQL-compliant conditional expressions available in PostgreSQL.
Tip
如果您有超出了这些条件表达式的能力之外的需求，则可以考虑使用更具表现力的编程语言编写服务器端函数。
If your needs go beyond the capabilities of these conditional expressions, you might want to consider writing a server-side function in a more expressive programming language.
Note
尽管 COALESCE、GREATEST 和 LEAST 在语法上与函数类似，但它们不是普通函数，因此不能与显式 VARIADIC 数组参数一起使用。
Although COALESCE, GREATEST, and LEAST are syntactically similar to functions, they are not ordinary functions, and thus cannot be used with explicit VARIADIC array arguments.
9.18.1. CASE #
SQL CASE 表达式是一个泛型条件表达式，类似于其他编程语言中的 if/else 语句：
The SQL CASE expression is a generic conditional expression, similar to if/else statements in other programming languages:
CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
ENDCASE 子句可以在任何表达式有效的地方使用。每个 condition 都是返回 boolean 结果的表达式。如果条件的结果为真，则 CASE 表达式的值为条件后的 result，并且不会处理 CASE 表达式的其余部分。如果条件的结果不为真，则以相同的方式检查任何后续 WHEN 子句。如果没有 WHEN condition 生成真，则 CASE 表达式的值为 ELSE 子句的 result。如果省略 ELSE 子句并且没有条件为真，则结果为 null。
CASE clauses can be used wherever an expression is valid. Each condition is an expression that returns a boolean result. If the condition’s result is true, the value of the CASE expression is the result that follows the condition, and the remainder of the CASE expression is not processed. If the condition’s result is not true, any subsequent WHEN clauses are examined in the same manner. If no WHEN condition yields true, the value of the CASE expression is the result of the ELSE clause. If the ELSE clause is omitted and no condition is true, the result is null.
示例：
An example:
SELECT * FROM test;
a
---
1
2
3
SELECT a,
CASE WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
ELSE 'other'
END
FROM test;
a | case
---+-------
1 | one
2 | two
3 | other所有 _result_表达式的类型必须可转换为单个输出类型。有关更多详细信息，请参阅 Section 10.5。
The data types of all the result expressions must be convertible to a single output type. See Section 10.5 for more details.
有一个“简单”形式的 CASE 表达式，它是上面通用形式的一个变体：
There is a “simple” form of CASE expression that is a variant of the general form above:
CASE expression
WHEN value THEN result
[WHEN ...]
[ELSE result]
END计算第一个 expression，然后将其与 WHEN 子句中的每个 value 表达式进行比较，直到找到一个与之相等的表达式为止。如果没有找到匹配项，则返回 ELSE 子句的 result（或 null 值）。这与 C 中的 switch 语句类似。
The first expression is computed, then compared to each of the value expressions in the WHEN clauses until one is found that is equal to it. If no match is found, the result of the ELSE clause (or a null value) is returned. This is similar to the switch statement in C.
可以使用简单的 CASE 语法编写上面的示例：
The example above can be written using the simple CASE syntax:
SELECT a,
CASE a WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
END
FROM test;
a | case
---+-------
1 | one
2 | two
3 | otherCASE 表达式不会计算确定结果不需要的任何子表达式。例如，这是避免除以零失败的一种可能方式：
A CASE expression does not evaluate any subexpressions that are not needed to determine the result. For example, this is a possible way of avoiding a division-by-zero failure:
SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;Note
如 Section 4.2.14中所述，在诸如此类情况下，表达式的子表达式在不同的时间进行求值，因此“_CASE_仅求值必要的子表达式”这一原则并不是坚不可摧的。例如，一个常量_1/0_子表达式通常会在计划时间导致除以零的失败，即使它处于_CASE_当中，并且在运行时永远不会输入当中。
As described in Section 4.2.14, there are various situations in which subexpressions of an expression are evaluated at different times, so that the principle that “CASE evaluates only necessary subexpressions” is not ironclad. For example a constant 1/0 subexpression will usually result in a division-by-zero failure at planning time, even if it’s within a CASE arm that would never be entered at run time.
9.18.2. COALESCE #
COALESCE(value [, ...])COALESCE 函数返回其第一个不为 null 的参数。仅当所有参数都为 null 时才返回 null。在为显示检索数据时，它通常用于用 null 值替换默认值，例如：
The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display, for example:
SELECT COALESCE(description, short_description, '(none)') ...如果它不为 null，则返回 description；否则，如果它不为 null，则返回 short_description；否则，返回 (none)。
This returns description if it is not null, otherwise short_description if it is not null, otherwise (none).
所有参数都必须可转换为公共数据类型，该类型将是结果的类型（有关详细信息，请参阅 Section 10.5）。
The arguments must all be convertible to a common data type, which will be the type of the result (see Section 10.5 for details).
与 CASE 表达式一样，COALESCE 仅计算确定结果所需的参数；也就是说，不会计算第一个非空参数右侧的参数。此 SQL 标准函数提供了类似于 NVL 和 IFNULL 的功能，后者在一些其他数据库系统中使用。
Like a CASE expression, COALESCE only evaluates the arguments that are needed to determine the result; that is, arguments to the right of the first non-null argument are not evaluated. This SQL-standard function provides capabilities similar to NVL and IFNULL, which are used in some other database systems.
9.18.3. NULLIF #
NULLIF(value1, value2)如果 value1 等于 value2，则 NULLIF 函数返回一个空值；否则，返回 value1。这可用于对上面给出的 COALESCE 示例执行逆操作：
The NULLIF function returns a null value if value1 equals value2; otherwise it returns value1. This can be used to perform the inverse operation of the COALESCE example given above:
SELECT NULLIF(value, '(none)') ...在此示例中，如果 value 为 (none)，则返回空值，否则返回 value 的值。
In this example, if value is (none), null is returned, otherwise the value of value is returned.
两个参数必须为可比较类型。具体而言，它们之间的比较完全等同于编写 value1 = value2 ，因此必须有合适的 = 操作符可用。
The two arguments must be of comparable types. To be specific, they are compared exactly as if you had written value1_ = value2, so there must be a suitable _= operator available.
结果与第一个参数的类型相同——但有微妙之处。实际返回的是隐式 = 运算符的第一个参数，并且在某些情况下，它会被提升为匹配第二个参数的类型。例如，NULLIF(1, 2.2) 会生成 numeric，因为没有 integer = numeric 运算符，只有 numeric = numeric。
The result has the same type as the first argument — but there is a subtlety. What is actually returned is the first argument of the implied = operator, and in some cases that will have been promoted to match the second argument’s type. For example, NULLIF(1, 2.2) yields numeric, because there is no integer = numeric operator, only numeric = numeric.
9.18.4. GREATEST and LEAST #
GREATEST(value [, ...])LEAST(value [, ...])_GREATEST_和 _LEAST_函数从任意数量的表达式的列表中选择最大值或最小值。所有表达式都必须可转换为公共数据类型，该类型将是结果的类型（有关详细信息，请参阅 Section 10.5）。
The GREATEST and LEAST functions select the largest or smallest value from a list of any number of expressions. The expressions must all be convertible to a common data type, which will be the type of the result (see Section 10.5 for details).
参数列表中的 NULL 值会被忽略。仅当所有表达式都计算为 NULL 时，结果才会为 NULL。（这与 SQL 标准不同。根据标准，如果任何参数为 NULL，则返回值为 NULL。其他某些数据库也遵循这种方式。）
NULL values in the argument list are ignored. The result will be NULL only if all the expressions evaluate to NULL. (This is a deviation from the SQL standard. According to the standard, the return value is NULL if any argument is NULL. Some other databases behave this way.)