Postgres NOT in array

Question

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

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

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

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 ALL is "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.

Augmenting the ALL/ANY Answers

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

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

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 <> all('{1,2,3}') on the other hand makes sure all values 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.

Beware of NULLs

Both ALL:

(some_value != ALL(some_array))

And ANY:

NOT (some_value = ANY(some_array))

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

(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