CODEWITHSUNDEEP
postgresql
Abelisto
Abelisto

Reputation: 15614

PostgreSQL Exclusion constraint with comparison predicate

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

Upvotes: 3

Views: 534

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246578

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.

Upvotes: 4

Related Questions