Postgres is NOT in an array

Question

I use my own Postgres array type and try to find entries that have no identifier in the identifiers of the recipients of the array.

I can find where they are IN:

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

But this does not work:

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

What is the correct way to test this condition?

Answer 1

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

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

Answer 2

You can rotate it a bit and say that "3 is not equal to all identifiers":

 where 3 != all (recipient_ids) 

From the exact guide :

9.21.4. ALL (array)

 expression operator ALL (array expression) 

The right part is an expression in brackets, which should give the value of the array. The left expression is evaluated and compared with each element of the array using the given operator , which should lead to a logical result. The result of ALL is "true" if all comparisons give the value true (including the case when the array has zero elements). The result is false if any false result is found.

Answer 3

not (3 = any(recipient_ids)) ?

Answer 4

Note that ANY / ALL statements will not work with array indices. If pointers mean:

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

and negative:

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

Then an index can be created, for example:

 CREATE INDEX recipient_ids_idx on tableName USING GIN(recipient_ids) 

Answer 5

update:

as in postgres 9.3,

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

IE

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