CODEWITHSUNDEEP
arrayspostgresql
user577808
user577808

Reputation: 2527

Postgres NOT in array

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?

Upvotes: 181

Views: 177240

Answers (9)

Zegarek
Zegarek

Reputation: 26656

Since 9.5 array_position() is not null can also safely check for null containment.

SELECT COUNT(*) FROM messages WHERE (array_position(recipient_ids, your_val) IS null)

array_position ( anycompatiblearray, anycompatible [, integer ] ) → integer

Returns the subscript of the first occurrence of the second argument in the array, or NULL if it's not present. If the third argument is given, the search begins at that subscript. The array must be one-dimensional. Comparisons are done using IS NOT DISTINCT FROM semantics, so it is possible to search for NULL.

There's no operator in GIN index array_ops that uses this function, so the price for using it in a query is that it won't benefit from the index. Demo at db<>fiddle:

select array_position(array[null],null) is not null;
?column?
TRUE

<@,&& and @> array operators as well as IN(), SOME() and ANY() use regular = that yields null if there's null on either side of the operator, which is why none of the examples below works, even though all might seem like they could/should:

select null=any(array[null,''])               as "any",
       null=some(array[null,''])              as "some",
       null=all(array[null,null])             as "all",
       null in (null,'')                      as "in",
       null in (select unnest(array[null,'']))as "in(2)",
       array[null] <@ array[null,'']          as "<@",
       array[null] && array[null,'']          as "&&";
any some all in in(2) <@ &&
null null null null null FALSE FALSE

Upvotes: 0

user1546488
user1546488

Reputation: 61

Use the following query select id from Example where NOT (id = ANY ('{1, 2}'))

Upvotes: 3

ThomasH
ThomasH

Reputation: 23536

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.

Upvotes: 44

isapir
isapir

Reputation: 23610

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

Upvotes: 44

Rooster
Rooster

Reputation: 10077

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];

Upvotes: 20

jamming james
jamming james

Reputation: 31

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)

Upvotes: 3

Markus Mikkolainen
Markus Mikkolainen

Reputation: 3497

not (3 = any(recipient_ids))?

Upvotes: 14

Frank Farmer
Frank Farmer

Reputation: 39386

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

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

Upvotes: 237

mu is too short
mu is too short

Reputation: 434965

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.

Upvotes: 65

Related Questions