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

On Fri, Dec 7, 2018 at 5:21 AM Thomas Kellerer <spam_eater(at)gmx(dot)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
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.