Re: != ANY(array) does not behave as expected

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: != ANY(array) does not behave as expected
Date: 2018-12-07 12:48:45
Message-ID: ba26cd81-487e-71f3-86ae-22dedd08b577@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Chris Wilson schrieb am 07.12.2018 um 13:39:
> However, if we try to invert it by using the != operator, then we get unexpected results:
>
> select * from foo where id NOT IN (1, 2); /* returns row 3 only, as expected */
> select * from foo where id != ANY (ARRAY[1, 2]); /* returns all rows, unexpected */

id <> ANY (...) means: return "true" if at least one of the elements is not equal to the value on the left side.

What you are looking for is the ALL operator

select *
from foo
where id <> ALL (ARRAY[1, 2]);

That is essentially the equivalent to NOT IN

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Kellerer 2018-12-07 13:20:55 Re: != ANY(array) does not behave as expected
Previous Message Chris Wilson 2018-12-07 12:39:50 != ANY(array) does not behave as expected