9.2. Comparison Functions and Operators

9.2. Comparison Functions and Operators

9.2.比较函数和运算符

The usual comparison operators are available, as shown in Table 9.1.

常用的比较运算符可用，如表9.1所示。

转存失败重新上传取消

转存失败重新上传取消

Note 

注

The != operator is converted to <> in the parser stage. It is not possible to implement != and <> operators that do different things.

在解析阶段，!=会解析为<>。!=和<>总是等效。

 

Comparison operators are available for all relevant data types. All comparison operators are binary operators that return values of type boolean; expressions like 1 < 2 < 3 are not valid (because there is no < operator to compare a Boolean value with 3).

比较运算符可用于所有相关数据类型。 所有比较运算符都是二进制运算符，它们返回boolean类型的值。 1 <2 <3之类的表达式无效（因为<运算符无法对布尔值与3进行比较）。

 

There are also some comparison predicates, as shown in Table 9.2. These behave much like operators,but have special syntax mandated by the SQL standard.

还有一些比较谓词，如表9.2所示。 它们的行为很像运算符，但是具有SQL标准规定的特殊语法。

 

转存失败重新上传取消

The BETWEEN predicate simplifies range tests:

BETWEEN谓词简化了范围测试：

 

a BETWEEN x AND y

 

is equivalent to

等效于：

 

a >= x AND a <= y

 

Notice that BETWEEN treats the endpoint values as included in the range. NOT BETWEEN does the opposite comparison:

请注意，BETWEEN将端点值视为包含在范围内。 NOT BETWEEN做相反的比较：

 

a NOT BETWEEN x AND y

 

 

is equivalent to

等效于：

 

a < x OR a > y

 

BETWEEN SYMMETRIC is like BETWEEN except there is no requirement that the argument to the left of AND be less than or equal to the argument on the right. If it is not, those two arguments are automatically swapped, so that a nonempty range is always implied.

BETWEEN SYMMETRIC与BETWEEN相似，只是不要求AND左边的参数小于或等于右边的参数。 如果不是，则会自动交换这两个参数，以便始终为一个非空范围。

 

Ordinary comparison operators yield null (signifying “unknown”), not true or false, when either input is null. For example, 7 = NULL yields null, as does 7 <> NULL. When this behavior is not suitable,use the IS [ NOT ] DISTINCT FROM predicates:

当任一输入为null时，普通比较运算符将生成null（表示“未知”），而不是true或false。 例如，7 = NULL和7 <> NULL都会产生null。 如果此行为不合适，请使用IS [NOT] DISTINCT FROM谓词：

 

a IS DISTINCT FROM b

a IS NOT DISTINCT FROM b

 

For non-null inputs, IS DISTINCT FROM is the same as the <> operator. However, if both inputs are null it returns false, and if only one input is null it returns true. Similarly, IS NOT DISTINCT FROM is identical to = for non-null inputs, but it returns true when both inputs are null, and false when only one input is null. Thus, these predicates effectively act as though null were a normal data value,rather than “unknown”.

对于非空输入，IS DISTINCT FROM与<>运算符相同。 但是，如果两个输入均为null，则返回false，如果只有一个输入为null，则返回true。 类似地，对于非空输入，IS NOT DISTINCT FROM与=相同，但是当两个输入均为空时，它返回true；而当只有一个输入为空时，它返回false。 因此，这些谓词有效地表现为空值是正常数据值，而不是“未知”。

 

To check whether a value is or is not null, use the predicates:

要检查值是否为空，请使用谓词：

 

expression IS NULL

expression IS NOT NULL

 

or the equivalent, but nonstandard, predicates:

或等同，但非标准的谓词：

 

expression ISNULL

expression NOTNULL

 

Do not write expression = NULL because NULL is not “equal to” NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.)

不要写expression = NULL，因为NULL不等于NULL。 （空值表示一个未知值，而两个未知值不知道是否相等。）

 

Tip

小贴士

Some applications might expect that expression = NULL returns true if expression evaluates to the null value. It is highly recommended that these applications be modified to comply with the SQL standard. However, if that cannot be done the transform_null_equals configuration variable is available. If it is enabled, PostgreSQL will convert x = NULL clauses to x IS NULL.

如果表达式的计算结果为空值，则某些应用程序可能期望expression = NULL返回true。强烈建议修改这些应用程序以符合SQL标准。但是，如果不能修改，则可以使用transform_null_equals配置变量。如果启用该变量，则PostgreSQL会将x = NULL子句转换为x IS NULL。

 

If the expression is row-valued, then IS NULL is true when the row expression itself is null or when all the row's fields are null, while IS NOT NULL is true when the row expression itself is non-null and all the row's fields are non-null. Because of this behavior, IS NULL and IS NOT NULL do not always return inverse results for row-valued expressions; in particular, a row-valued expression that contains both null and non-null fields will return false for both tests. In some cases,it may be preferable to write row IS DISTINCT FROM NULL or row IS NOT DISTINCT FROM NULL, which will simply check whether the overall row value is null without any additional tests on the row fields.

如果表达式为行值，则当行表达式本身为null或所有行的字段为null时，IS NULL为true；而当行表达式本身为非null且所有行的字段为非null时，IS NOT NULL为true。由于这种行为，IS NULL和IS NOT NULL并不总是返回行值表达式的反结果。特别是，同时包含null和non-null字段的行值表达式对于两个测试都将返回false。在某些情况下，最好将行IS DISTINCT FROM NULL或行IS NOT DISTINCT FROM NULL写入，这将简单地检查整个行值是否为null，而无需对行字段进行任何其他测试。

 

Boolean values can also be tested using the predicates

布尔值也可以使用谓词进行测试：

 

boolean_expression IS TRUE

boolean_expression IS NOT TRUE

boolean_expression IS FALSE

boolean_expression IS NOT FALSE

boolean_expression IS UNKNOWN

boolean_expression IS NOT UNKNOWN

 

These will always return true or false, never a null value, even when the operand is null. A null input is treated as the logical value “unknown”. Notice that IS UNKNOWN and IS NOT UNKNOWN are effectively the same as IS NULL and IS NOT NULL, respectively, except that the input expression must be of Boolean type.

这些将始终返回true或false，即使操作数为null，也永远不会返回null值。 空输入被视为逻辑值“未知”。 请注意，IS UNKNOWN和IS NOT UNKNOWN实际上分别与IS NULL和IS NOT NULL相同，只是输入表达式必须为布尔类型。

 

Some comparison-related functions are also available, as shown in Table 9.3.

如表9.3所示，还提供了一些与比较相关的函数。