PostgreSQL Exclusion constraint with comparison predicate

Question

There is simple to mimic unique constraint like

create table foo(x int, exclude (x with =));

But how is it possible to use IS NOT DISTINCT FROM instead of = (so there is only one NULL value in the table)?

Creating function like f(anyelement, anyelement) and operator fails because null have unknown type. So yet another question: is it possible to wrap IS NOT DISTINCT FROM predicate into operator in PostgreSQL?

Be patient, I not searching an alternative solution, I know its a lot :)

Furthermore reading: Comparison operators

There might be a neater way, but can you specify the right type for the null? NULL::int — Ry-
– Ry- ♦, Commented Jan 15, 2018 at 4:28
@Abelisto: Why does that matter? The inputs won't have type unknown when you use it as a table constraint... — Nick Barnes
– Nick Barnes, Commented Jan 15, 2018 at 9:55
@Abelisto: Oh, then you may be thinking of this one, discussing rCTE solutions: stackoverflow.com/a/25536748/939860; with a link to this wiki page: wiki.postgresql.org/wiki/Loose_indexscan (latest updates there are from me as well). The keyword is "loose index scan". — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 15, 2018 at 16:42

Laurenz Albe · Accepted Answer · 2018-01-15 09:47:05Z

5

It is easy to create a function and an operator that corresponds to NOT DISTINCT TO:

CREATE FUNCTION ndist(anyelement, anyelement) RETURNS boolean
   IMMUTABLE CALLED ON NULL INPUT LANGUAGE sql
   AS 'SELECT $1 IS NOT DISTINCT FROM $2';

CREATE OPERATOR === (
   PROCEDURE = ndist,
   LEFTARG = anyelement,
   RIGHTARG = anyelement,
   COMMUTATOR = "==="
);

This will fail if both arguments are untyped NULLs:

test=> SELECT NULL === NULL;
ERROR:  could not determine polymorphic type because input has type unknown

One solution is to use overloading and define the same function and operator for every type you need:

CREATE FUNCTION ndist(integer, integer) RETURNS boolean
   IMMUTABLE CALLED ON NULL INPUT LANGUAGE sql
   AS 'SELECT $1 IS NOT DISTINCT FROM $2';

CREATE FUNCTION ndist(text, text) RETURNS boolean
   IMMUTABLE CALLED ON NULL INPUT LANGUAGE sql
   AS 'SELECT $1 IS NOT DISTINCT FROM $2';

CREATE OPERATOR === (
   PROCEDURE = ndist,
   LEFTARG = integer,
   RIGHTARG = integer,
   COMMUTATOR = "==="
);

CREATE OPERATOR === (
   PROCEDURE = ndist,
   LEFTARG = text,
   RIGHTARG = text,
   COMMUTATOR = "==="
);

Then the example will work:

test=> SELECT NULL === NULL;
 ?column? 
----------
 t
(1 row)

This is because the type resolution rules will prefer the operator on text in that case.

But all this will not allow you to create an exclusion constraint, because your operator is not associated with an operator class, which is necessary to determine what kind of index is to be used.

You would have to create a matching function for each of the btree index method strategies and define an operator class for btree with these functions.

answered Jan 15, 2018 at 9:47

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

Abelisto Over a year ago

I always forget that anything in PostgreSQL could be implicitly converted to/from text.

Erwin Brandstetter Over a year ago

@Abelisto: About implicit casts: dba.stackexchange.com/questions/194975/…

Collectives™ on Stack Overflow

PostgreSQL Exclusion constraint with comparison predicate

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related