PostgreSQL "IS [NOT] DISTINCT FROM" operator

Question

Let's suppose a table:

CREATE TABLE foo (
  id serial primary key
  , range int4range NOT NULL
  , barid integer references bar(id)
  , baz whatever... NOT NULL
  , EXCLUDE USING gist (range WITH &&, barid WITH =)
);

The exclusion constraint is supposed to not allow overlapping "range" values for the same "barid" value. But I would like it to treat "NULL" just like other values (so not allow overlappig ranges for diffrent records having barid = NULL). So given a = NULL and b = NULL, I would like to achieve "a IS NOT DISTINCT FROM b" type of behaviour , instead of "a = b" one (as described here). Does such an operator exist? At the moment I am able to achieve that only by using

EXCLUDE USING gist (range WITH &&, COALESCE(barid, -1) WITH =)

Answer 1

a IS NOT DISTINCT FROM b won't use an index. Ever.

So no, it doesn't exist.

Aside: null in a relational algebra means undefined or unknown. It does not mean no value, as in special value that actually is not one because inapplicable.

If you want null to mean the latter, make it not null and assign a special value to the "no value" item, e.g. 0 or -1. And add this special value to your referencing table too.

That way you'll be able to use your existing constraint.