Postgresql 中文操作指南

9.2. Comparison Functions and Operators #

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

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

Table 9.1. Comparison Operators

Operator

Description

datatype < datatype → boolean

Less than

datatype > datatype → boolean

Greater than

datatype ⇐ datatype → boolean

Less than or equal to

datatype >= datatype → boolean

Greater than or equal to

datatype = datatype → boolean

Equal

datatype <> datatype → boolean

Not equal

datatype != datatype → boolean

Not equal

Note

<> 是“不等于”的标准 SQL 表示法。!= 是一个别名，它在解析的极早期阶段被转换为 <>。因此，不可能实现执行不同操作的 != 和 <> 运算符。

<> is the standard SQL notation for “not equal”. != is an alias, which is converted to <> at a very early stage of parsing. Hence, it is not possible to implement != and <> operators that do different things.

这些比较运算符适用于所有具有自然比较规则的内置数据类型，包括数字、字符串和日期/时间类型。此外，如果组件数据类型可比较，则可以比较数组、复合类型和范围。

These comparison operators are available for all built-in data types that have a natural ordering, including numeric, string, and date/time types. In addition, arrays, composite types, and ranges can be compared if their component data types are comparable.

通常也可以比较相关数据类型的值；例如，integer > bigint 将起作用。某些此类情况由“跨类型”比较运算符直接实现，但如果没有此类运算符，解析器将强制将低级别类型转换为更高级别类型，并应用后者的比较运算符。

It is usually possible to compare values of related data types as well; for example integer > bigint will work. Some cases of this sort are implemented directly by “cross-type” comparison operators, but if no such operator is available, the parser will coerce the less-general type to the more-general type and apply the latter’s comparison operator.

如上所示，所有比较运算符都是返回 boolean 类型的二元运算符。因此，1 < 2 < 3 之类的表达式无效（因为没有 < 运算符可以将布尔值与 3 进行比较）。使用下面显示的 BETWEEN 谓词来执行范围测试。

As shown above, all comparison operators are binary operators that return values of type boolean. Thus, expressions like 1 < 2 < 3 are not valid (because there is no < operator to compare a Boolean value with 3). Use the BETWEEN predicates shown below to perform range tests.

同样有一些比较谓词，如 Table 9.2 所示。这些行为与运算符非常相似，但也具有 SQL 标准要求的特殊语法。

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.

Table 9.2. Comparison Predicates

Predicate

Description

Example(s)

datatype BETWEEN datatype AND datatype → boolean

Between (inclusive of the range endpoints).

2 BETWEEN 1 AND 3 → t

2 BETWEEN 3 AND 1 → f

datatype NOT BETWEEN datatype AND datatype → boolean

Not between (the negation of BETWEEN).

2 NOT BETWEEN 1 AND 3 → f

datatype BETWEEN SYMMETRIC datatype AND datatype → boolean

Between, after sorting the two endpoint values.

2 BETWEEN SYMMETRIC 3 AND 1 → t

datatype NOT BETWEEN SYMMETRIC datatype AND datatype → boolean

Not between, after sorting the two endpoint values.

2 NOT BETWEEN SYMMETRIC 3 AND 1 → f

datatype IS DISTINCT FROM datatype → boolean

Not equal, treating null as a comparable value.

1 IS DISTINCT FROM NULL → t (rather than NULL)

NULL IS DISTINCT FROM NULL → f (rather than NULL)

datatype IS NOT DISTINCT FROM datatype → boolean

Equal, treating null as a comparable value.

1 IS NOT DISTINCT FROM NULL → f (rather than NULL)

NULL IS NOT DISTINCT FROM NULL → t (rather than NULL)

datatype IS NULL → boolean

Test whether value is null.

1.5 IS NULL → f

datatype IS NOT NULL → boolean

Test whether value is not null.

'null' IS NOT NULL → t

datatype ISNULL → boolean

Test whether value is null (nonstandard syntax).

datatype NOTNULL → boolean

Test whether value is not null (nonstandard syntax).

boolean IS TRUE → boolean

Test whether boolean expression yields true.

true IS TRUE → t

NULL::boolean IS TRUE → f (rather than NULL)

boolean IS NOT TRUE → boolean

Test whether boolean expression yields false or unknown.

true IS NOT TRUE → f

NULL::boolean IS NOT TRUE → t (rather than NULL)

boolean IS FALSE → boolean

Test whether boolean expression yields false.

true IS FALSE → f

NULL::boolean IS FALSE → f (rather than NULL)

boolean IS NOT FALSE → boolean

Test whether boolean expression yields true or unknown.

true IS NOT FALSE → t

NULL::boolean IS NOT FALSE → t (rather than NULL)

boolean IS UNKNOWN → boolean

Test whether boolean expression yields unknown.

true IS UNKNOWN → f

NULL::boolean IS UNKNOWN → t (rather than NULL)

boolean IS NOT UNKNOWN → boolean

Test whether boolean expression yields true or false.

true IS NOT UNKNOWN → t

NULL::boolean IS NOT UNKNOWN → f (rather than NULL)

BETWEEN 谓词简化了范围测试：

The BETWEEN predicate simplifies range tests:

a BETWEEN x AND y

等效于

is equivalent to

a >= x AND a <= y

注意，BETWEEN 将端点值视为包含在范围内。BETWEEN SYMMETRIC 与 BETWEEN 类似，只不过没有要求 AND 左边的参数小于或等于右边参数。如果没有要求，则这两个参数将自动交换，如此一来就始终隐含了非空范围。

Notice that BETWEEN treats the endpoint values as included in the range. 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 的各种变量是基于普通比较运算符实现的，因此适用于可以比较的任何数据类型。

The various variants of BETWEEN are implemented in terms of the ordinary comparison operators, and therefore will work for any data type(s) that can be compared.

Note

在 BETWEEN 语法中使用 AND 会与作为逻辑运算符的 AND 使用产生歧义。为了解决此问题，只有有限的表达式类型集合允许作为 BETWEEN 的第二个参数。如果需要在 BETWEEN 中编写更复杂的子表达式，请在子表达式周围编写圆括号。

The use of AND in the BETWEEN syntax creates an ambiguity with the use of AND as a logical operator. To resolve this, only a limited set of expression types are allowed as the second argument of a BETWEEN clause. If you need to write a more complex sub-expression in BETWEEN, write parentheses around the sub-expression.

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

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:

a IS DISTINCT FROM b
a IS NOT DISTINCT FROM b

对于非空输入，IS DISTINCT FROM 与 <> 运算符相同。但是，如果两个输入都是空的，它将返回假，如果只有一个输入是空的，它将返回真。类似地，对于非空输入，IS NOT DISTINCT FROM 与 = 相同，但当两个输入都是空时，它将返回真，当只有一个输入是空时，它将返回假。因此，这些谓词有效地起作用，好像 NULL 是一个普通数据值，而不是“未知”。

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”.

要检查值是否为或不为 null，请使用谓词：

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

不要编写 not = NULL_，因为 NULL 不“等于” NULL 。（null 值表示未知值，并且不知道两个未知值是否相等。）

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.)

Tip

某些应用程序可能希望当 expression 求值为 null 值时， _expression = NULL_ 返回 true。强烈建议修改这些应用程序以符合 SQL 标准。但是，如果无法做到这一点，则可以使用 transform_null_equals 配置变量。如果启用它，PostgreSQL 将 x = NULL 子句转换为 x IS NULL 。

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 或行中的所有字段为 null 时 IS NULL 的值为 true，而当行表达式本身为非 null 且行中的所有字段都为非 null 时 IS NOT NULL 的值为 true。由于此行为，IS NULL 和 IS NOT NULL 不会总对行值表达式返回相反的结果；特别是，在行值表达式中同时包含 null 和非 null 字段时，两个测试都将会返回 false。在某些情况下，最好写成 row IS DISTINCT FROM NULL 或 row IS NOT DISTINCT FROM NULL，这样就可以直接检查整体行值是否为 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.

还可以使用谓词来测试布尔值

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

无论操作数是否为 null，这些表达式总会返回 true 或 false，绝不会返回 null 值。空输入被视为逻辑值“未知”。请注意，IS UNKNOWN 和 IS NOT UNKNOWN 分别与 IS NULL 和 IS NOT NULL 实际上是相同的，只是输入表达式必须为布尔类型。

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.

一些与比较相关的函数也可用，如 Table 9.3 所示。

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

Table 9.3. Comparison Functions

Function

Description

Example(s)

num_nonnulls ( VARIADIC "any" ) → integer

Returns the number of non-null arguments.

num_nonnulls(1, NULL, 2) → 2

num_nulls ( VARIADIC "any" ) → integer

Returns the number of null arguments.

num_nulls(1, NULL, 2) → 1