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

>>>>> "Chris" == Chris Wilson <chris(dot)wilson(at)cantabcapital(dot)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)