Postgres NOT in array

Question

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

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?

Answer 1

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.

Answer 2

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.

Answer 3

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

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

Answer 4

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

Answer 5

not (3 = any(recipient_ids))?

Answer 6

an update:

as of postgres 9.3,

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

IE.

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