Postgres Pro
Downloads
Home   >   mailing lists

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

!= ANY(array) does not behave as expected

From
Chris Wilson
Date:

Dear Postgres users,

 

I like using ANY(array) instead of IN (…), as we can pass the array as binary data, avoiding the need to render its contents (which might be integers) into a SQL string, for Postgres to parse them back into integers again, and it also works with an empty list. For example:

 

create table foo (id integer);

insert into foo (id) values (1), (2), (3);

select * from foo where id IN (1, 2); /* returns rows 1 and 2 */

select * from foo where id = ANY (ARRAY[1, 2]); /* returns rows 1 and 2 */

 

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 */

 

I don’t really understand why this is the case. I guess that perhaps an ANY-object has an equality operator that tests for membership of the array, but its inequality operator does something different. I don’t understand what it’s doing at all, or how it might be useful. Could anyone enlighten me?

 

I did find a workaround that may be useful to others (perhaps something to add to the documentation?):

 

select * from foo where NOT(id = ANY (ARRAY[1, 2])); /* returns row 3 only, as expected */

 

In a search for a solution or workaround, to pass arrays of IDs to exclude into queries, I noted that the manual says:

 

expression NOT IN (subquery)

The right-hand side is a parenthesized subquery, which must return exactly one column.

I tried to pass an expression that returns one column, but that failed:

 

select * from foo where id NOT IN (unnest(ARRAY[1, 2])); /* fails with “set-returning functions are not allowed in WHERE” */

 

But if I use a real subquery then it succeeds:

 

select * from foo where id NOT IN (SELECT * FROM unnest(ARRAY[1, 2])) /* returns row 3 only */

 

If the current behaviour of != ANY (ARRAY…) is not useful, then is there any support for (or opposition to) fixing it? And is it a bug that one can’t use unnest in a NOT IN expression in the WHERE clause?

 

Thanks, Chris.

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

From
Thomas Kellerer
Date:
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

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

From
Thomas Kellerer
Date:
Thomas Kellerer schrieb am 07.12.2018 um 13:48:
> 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

See here for a longer explanation:

   https://stackoverflow.com/a/10675636

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

From
Ken Tanzer
Date:


On Fri, Dec 7, 2018 at 5:21 AM Thomas Kellerer <spam_eater@gmx.net> wrote:
Thomas Kellerer schrieb am 07.12.2018 um 13:48:
> 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
 See here for a longer explanation:

   https://stackoverflow.com/a/10675636


 
You can also just do this:

select * from foo where NOT id = ANY (ARRAY[1, 2]); 

Cheers,
Ken
--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

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

From
Andrew Gierth
Date:
>>>>> "Chris" == Chris Wilson <chris.wilson@cantabcapital.com> writes:

 Chris> However, if we try to invert it by using the != operator, then
 Chris> we get unexpected results:

Mr. De Morgan would like a word.
https://en.wikipedia.org/wiki/De_Morgan%27s_laws

In short, if you have a condition of the form (a OR b) and you want to
negate it, then you find that:

    NOT (a OR b)  is equivalent to  (NOT a) AND (NOT b)

Since  x = ANY (array[1,2])  is equivalent to (x = 1) OR (x = 2), then
the negation would be (x != 1) AND (x != 2), not OR.

Which can be conveniently expressed as  x != ALL (array[1,2]).

So just as you interchange AND and OR when inverting the sense of a
condition, you also interchange ALL and ANY for exactly the same
reasons.

 Chris> expression NOT IN (subquery)

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_NOT_IN

 Chris> And is it a bug that one can't use unnest in a NOT IN expression
 Chris> in the WHERE clause?

No.

-- 
Andrew (irc:RhodiumToad)