I'm wondering if the negator clause of CREATE OPERATOR specifies how the negator can be interpreted, or how it will be interpreted. For example, if I have an operator >= and I set the negator to the operator <, will NOT(x >= y) always be interpreted as x < y, or does it just mean that it will be interpreted that way when it's advantageous.

I'm asking because I'd like to be able to be able to use NOT(x op y) in my queries as something other than the complement of x op y. Is this possible and/or would it be playing with fire?

Concretely, I have a composite type with a subcolumn match_id and a subcolumn second. I want to be able to use, for example, NOT(x >= y) to mean x.match_id = y.match_id AND x.second < y.second, which would be encoded in the operator <.


Starting with PostgreSQL documentation about Negators:

36.13.2. NEGATOR

The NEGATOR clause, if provided, names an operator that is the negator of the operator being defined. We say that operator A is the negator of operator B if both return Boolean results and (x A y) equals NOT (x B y) for all possible inputs x, y. Notice that B is also the negator of A. For example, < and >= are a negator pair for most data types. An operator can never validly be its own negator.


The way the NEGATOR is supposed to behave does not match your intended usage. I guess this could result in some execution plans not giving the intended results.

Again, according to the docs:

Providing a negator is very helpful to the query optimizer since it allows expressions like NOT (x = y) to be simplified into x <> y. This comes up more often than you might think, because NOT operations can be inserted as a consequence of other rearrangements.

The way the text has been written, I understand that this implies that the planner might simpify a clause like NOT(a op b) into a negator_op b (or even the other way around, as commented out by @craig-ringer); but it also might not do it. The planner is allowed to make the simplification, but not coerced to or has an obligation to perform the conversion.

I suggest that, if you need different kind of comparisons, that you create other opeartors (let's say <% >% <=% >=% or something like that), with corresponding negators if they make sense.

  • Thanks for the reply. Based on their language, I think you're right. – user3747260 May 6 '17 at 21:49
  • 1
    It might also choose to replace the negator with the original operator. – Craig Ringer May 7 '17 at 3:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.