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

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

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)