3

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

Abelisto
  • 14,826
  • 2
  • 33
  • 41
  • There might be a neater way, but can you specify the right type for the null? `NULL::int` – Ry- Jan 15 '18 at 04:28
  • @Ryan Surely. But I asking for general solution. – Abelisto Jan 15 '18 at 04:31
  • Related (no dupe, but may be useful): https://dba.stackexchange.com/questions/60342/can-is-distinct-from-be-combined-with-any-or-all-somehow/60357. This question might fit dba.SE, too. – Erwin Brandstetter Jan 15 '18 at 04:57
  • (@ErwinBrandstetter, last time I searching for Postgres wik about distinct on (...) Could you post this link?) – Abelisto Jan 15 '18 at 05:09
  • 2
    You mean this one? https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/7630564#7630564 – Erwin Brandstetter Jan 15 '18 at 05:22
  • A not very comfortable solution would be to define your own operator with `CREATE OPERATOR`. – clemens Jan 15 '18 at 06:01
  • @clemens Yes it is alternative to create operators `int - int`, `text - text`, `json-json` `range-range` etc. – Abelisto Jan 15 '18 at 08:49
  • @ErwinBrandstetter (No. Not your answer (but it was in some of it by link-link-link-...), but PostgreSQL wiki. There was brief simple explanation why `group by` and `distinct on` are slow and how to do it faster with recursive CTE) – Abelisto Jan 15 '18 at 09:08
  • What's the problem with your function exactly? There shouldn't be any "unknown types" involved when evaluating an exclusion constraint... – Nick Barnes Jan 15 '18 at 09:37
  • @NickBarnes `my_function(1, null)` and `my_function(null, 1)` works fine. but `my_function(null, null)` caused an error `could not determine polymorphic type because input has type "unknown"`. – Abelisto Jan 15 '18 at 09:41
  • 1
    @Abelisto: Why does that matter? The inputs won't have type `unknown` when you use it as a table constraint... – Nick Barnes Jan 15 '18 at 09:55
  • @NickBarnes True. But if i wrote some code I'd like to have it reusable as most as possible. Thanks for your time in anywhere. – Abelisto Jan 15 '18 at 11:03
  • 1
    @Abelisto: Oh, then you may be thinking of this one, discussing rCTE solutions: https://stackoverflow.com/a/25536748/939860; with a link to this wiki page: https://wiki.postgresql.org/wiki/Loose_indexscan (latest updates there are from me as well). The keyword is "loose index scan". – Erwin Brandstetter Jan 15 '18 at 16:42
  • @ErwinBrandstetter Yes! It was nightmare that I remember it but can not find :) Thanks a lot. – Abelisto Jan 15 '18 at 19:34

1 Answers1

4

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263