postgresql Postgres 不在数组中

Postgres NOT in array

提问by user577808

I'm using Postgres' native array type, and trying to find the records where the ID is not in the array recipient IDs.

我正在使用 Postgres 的本机数组类型，并尝试查找 ID 不在数组收件人 ID 中的记录。

I can find where they are IN:

我可以找到他们在哪里：

SELECT COUNT(*) FROM messages WHERE (3 = ANY (recipient_ids))

But this doesn't work:

但这不起作用：

SELECT COUNT(*) FROM messages WHERE (3 != ANY (recipient_ids))
SELECT COUNT(*) FROM messages WHERE (3  = NOT ANY (recipient_ids))

What's the right way to test for this condition?

测试这种情况的正确方法是什么？

回答by Frank Farmer

SELECT COUNT(*) FROM "messages" WHERE NOT (3 = ANY (recipient_ids))

You can always negate WHERE (condition)with WHERE NOT (condition)

你总是可以否定WHERE (condition)与WHERE NOT (condition)

回答by mu is too short

You could turn it around a bit and say "3 is not equal to all the IDs":

你可以稍微改变一下并说“3 不等于所有的 ID”：

where 3 != all (recipient_ids)

From the fine manual:

从精美的手册：

9.21.4. ALL (array)

expression operator ALL (array expression)

The right-hand side is a parenthesized expression, which must yield an array value. The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. The result of ALLis "true" if all comparisons yield true (including the case where the array has zero elements). The result is "false" if any false result is found.

9.21.4. 所有（数组）

expression operator ALL (array expression)

右边是一个括号表达式，它必须产生一个数组值。使用给定的operator评估左侧表达式并将其与数组的每个元素进行比较，这必须产生布尔结果。ALL如果所有比较结果为真（包括数组具有零个元素的情况），则结果为“真”。如果发现任何错误结果，则结果为“false”。

回答by ThomasH

Augmenting the ALL/ANYAnswers

增加ALL/ANY答案

I prefer all solutions that use allor anyto achieve the result, appreciating the additional notes (e.g. about NULLs). As another augementation, here is a way to think about those operators.

我更喜欢所有使用all或any实现结果的解决方案，感谢附加注释（例如关于NULLs）。作为另一种扩展，这是考虑这些运算符的一种方式。

You can think about them as short-circuit operators:

您可以将它们视为短路运算符：

• all(array)goes through all the values in the array, comparing each to the reference value using the provided operator. As soon as a comparison yields false, the process ends with false, otherwise true. (Comparable to short-circuit logical and.)
• any(array)goes through all the values in the array, comparing each to the reference value using the provided operator. As soon as a comparison yields true, the process ends with true, otherwise false. (Comparable to short-circuit logical or.)

• all(array)遍历数组中的所有值，使用提供的运算符将每个值与参考值进行比较。一旦比较 yield false，该过程以 false 结束，否则为 true。（相当于短路逻辑and。）
• any(array)遍历数组中的所有值，使用提供的运算符将每个值与参考值进行比较。一旦比较产生true，该过程以真结束，否则为假。（相当于短路逻辑or。）

This is why 3 <> any('{1,2,3}')does not yield the desired result: The process compares 3 with 1 for inequality, which is true, and immediately returns true. A single value in the array different from 3 is enough to make the entire condition true. The 3 in the last array position is prob. never used.

这就是为什么3 <> any('{1,2,3}')没有产生预期结果的原因：该过程将 3 与 1 进行比较以表示不等式，结果为真，并立即返回真。数组中不同于 3 的单个值足以使整个条件成立。最后一个数组位置的 3 是 prob。没用过。

3 <> all('{1,2,3}')on the other hand makes sure allvalues are not equal 3. It will run through all comparisons that yield true up to an element that yields false (the last in this case), to return false as the overall result. This is what the OP wants.

3 <> all('{1,2,3}')另一方面，确保所有值不等于 3。它将运行所有产生 true 的比较，直到产生 false 的元素（在这种情况下是最后一个），以返回 false 作为整体结果。这就是 OP 想要的。

回答by Markus Mikkolainen

not (3 = any(recipient_ids))?

not (3 = any(recipient_ids))?

回答by Rooster

an update:

更新：

as of postgres 9.3,

从 postgres 9.3 开始，

you can use NOTin tandem with the @>(contains operator)to achieve this as well.

您也可以NOT与@>(contains operator)一起使用来实现此目的。

IE.

IE。

SELECT COUNT(*) FROM "messages" WHERE NOT recipient_ids @> ARRAY[3];

SELECT COUNT(*) FROM "messages" WHERE NOT recipient_ids @> ARRAY[3];

回答by isapir

Beware of NULLs

小心 NULL

Both ALL:

两者ALL：

(some_value != ALL(some_array))

And ANY:

并且ANY：

NOT (some_value = ANY(some_array))

Would work as long as some_arrayis not null. If the array might be null, then you must account for it with coalesce(), e.g.

只要some_array不为空就可以工作。如果数组可能为空，那么您必须使用coalesce() 对其进行说明，例如

(some_value != ALL(coalesce(some_array, array[]::int[])))

Or

或者

NOT (some_value = ANY(coalesce(some_array, array[]::int[])))

From the docs:

从文档：

If the array expression yields a null array, the result of ANY will be null

If the array expression yields a null array, the result of ALL will be null

如果数组表达式产生一个空数组，则 ANY 的结果将为空

如果数组表达式产生一个空数组，则 ALL 的结果将为空

回答by jamming james

Note that the ANY/ALL operators will not work with array indexes. If indexes are in mind:

请注意，ANY/ALL 运算符不适用于数组索引。如果考虑索引：

SELECT COUNT(*) FROM "messages" WHERE 3 && recipient_ids

and the negative:

和消极的：

SELECT COUNT(*) FROM "messages" WHERE NOT (3 && recipient_ids)

An index can then be created like:

然后可以像这样创建索引：

CREATE INDEX recipient_ids_idx on tableName USING GIN(recipient_ids)