Postgres NOT in array

user_577808 0
Viewed 118260

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?

postgresql arrays
7 Answers

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.

user_1274650
Related