Re: Negators for operators - Mailing list pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Jun 3, 2016 at 3:02 PM, David Fetter <david@fetter.org> wrote:
>> While constructing a somewhat hairy query with HAVING in it, I noticed
>> that a lot of operators don't have negators, which would have been
>> convenient for the class of queries I was constructing.  Further
>> investigation showed that while 380 of the built-in operators had
>> negators, 395 do not.

Really?

postgres=# select count(*) from pg_operator where oprresult = 'bool'::regtype and oprnegate = 0;count 
-------  135
(1 row)

The entire concept of negator is meaningless for operators that don't
return bool.

>> For some fraction I'll investigate if warranted, a negator makes no
>> sense.  For the rest, I'd like to propose adding negator operators
>> prefixed with '!', just as we have for the negators of regex-like
>> things.

> How about using NOT?

Indeed.

A quick look through the catalogs suggests that some fraction of these
cases may just be oversights, ie there is a suitable negator operator
but we just forgot to provide the link.  I'd be in favor of fixing those
by adding the links.  But in general, this proposal is going to mean
creating, and having to document, a lot of operators named like !|&>
... I seriously doubt that anyone's going to find that useful.

postgres=# select distinct oprname, obj_description(oid,'pg_operator') from pg_operator where oprresult =
'bool'::regtypeand oprnegate = 0 order by 1;
 
oprname |                 obj_description                 
---------+-------------------------------------------------&&      | overlaps&&      | overlaps (is subnet or
supernet)&<     | overlaps or is left of&<|     | overlaps or is below&>      | overlaps or is right of-|-     | is
adjacentto<       | less than<<      | contains<<      | is left of<<      | is subnet<<=     | is subnet or equal<<|
 | is below<=      | less than or equal<?>     | is contained by<@      | is contained by<@      | lseg on line<@
|point inside box<@      | point on line<@      | point within closed path, or point on open path<^      | is below<^
  | is below (allows touching)=       | equal=       | equal by area>       | greater than>=      | greater than or
equal>>     | is right of>>      | is supernet>>=     | is supernet or equal>^      | is above>^      | is above
(allowstouching)?       | key exists?#      | deprecated, use && instead?#      | intersect?&      | all keys exist?-
  | horizontal?-      | horizontally aligned?-|     | perpendicular?|      | any key exists?|      | vertical?|      |
verticallyaligned?||     | parallel@       | deprecated, use <@ instead@>      | contains@@      | text search match@@@
   | deprecated, use @@ instead|&>     | overlaps or is above|>>     | is above~       | deprecated, use @> instead~=
  | same as
 
(49 rows)

Most of those look pretty "meh" to me.
        regards, tom lane