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

From: Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
To: Chris Wilson <chris(dot)wilson(at)cantabcapital(dot)com>
Cc: "pgsql-general\(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: != ANY(array) does not behave as expected
Date: 2018-12-07 18:49:53
Message-ID: 87o99x2ne0.fsf@news-spur.riddles.org.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

>>>>> "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)

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2018-12-07 21:35:19 Re: Importing tab delimited text file using phpPgAdmin 5.1 GUI
Previous Message Ken Tanzer 2018-12-07 18:40:09 Re: != ANY(array) does not behave as expected