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

Chris · Accepted Answer · 2022-11-02 11:20:33Z

4

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.

edited Nov 2, 2022 at 11:20

Chris

139k139 gold badges316 silver badges293 bronze badges

answered Jan 29, 2014 at 12:18

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

flodin Over a year ago

How do you know this? Is it stated somewhere in the documentation? It seems like such an odd thing to just silently not support, when IS NULL can use indexes.

runeks Over a year ago

@flodin see the replies to this question on the Postgres mailing list: postgresql.org/message-id/…

Collectives™ on Stack Overflow

PostgreSQL "IS [NOT] DISTINCT FROM" operator

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related