4

After reviewing this answer, I have created the following custom operators:

CREATE OR REPLACE FUNCTION is_not_distinct_from(
    ANYELEMENT, 
    ANYELEMENT
)
RETURNS BOOLEAN AS
$$
SELECT $1 IS NOT DISTINCT FROM $2;
$$
LANGUAGE sql 
IMMUTABLE;

CREATE OPERATOR =!= (
    PROCEDURE = is_not_distinct_from(anyelement,anyelement),
    LEFTARG  = anyelement,
    RIGHTARG = anyelement,
    COMMUTATOR = =!=,
    NEGATOR = <!>
);

CREATE OR REPLACE FUNCTION is_distinct_from(
    ANYELEMENT, 
    ANYELEMENT
)
RETURNS BOOLEAN AS
$$
SELECT $1 IS DISTINCT FROM $2;
$$
LANGUAGE sql 
IMMUTABLE;

CREATE OPERATOR <!> (
    PROCEDURE = is_distinct_from(anyelement,anyelement),
    LEFTARG  = anyelement,
    RIGHTARG = anyelement,
    COMMUTATOR = <!>,
    NEGATOR = =!=
);

I have a table that allows two NULL values, and I would like to prevent duplicate records, treating NULL as a value.

CREATE TABLE "Foo" (
    "FooID" SERIAL PRIMARY KEY,
    "Foo" TEXT NOT NULL,
    "Bar" TIMESTAMPTZ
    "Baz" TIMESTAMPTZ
    EXCLUDE ("Foo" WITH =, "Bar" WITH =!=, "Baz" WITH =!=)
);

I get the following error:

ERROR: operator =!=(anyelement,anyelement) is not a member of operator family "datetime_ops" DETAIL: The exclusion operator must be related to the index operator class for the constraint.

I have reviewed the documentation (here and here) but I'm having a hard time comprehending the material.

Additionally, this question could be considered a duplicate of this one; however, the problem in that question was one of compatibility with other RDBMS... this question is specifically addressing how to handle the error above.

Community
  • 1
  • 1
losthorse
  • 1,530
  • 1
  • 13
  • 33
  • 1
    The opclass documentation is pretty useless without [this](http://www.postgresql.org/docs/current/static/xindex.html) and [this](http://www.postgresql.org/docs/current/static/gist-extensibility.html). The `btree_gist` extension's GiST implementation for `timestamp` is [here](https://github.com/postgres/postgres/blob/master/contrib/btree_gist/btree_gist--1.1.sql#L690) and [here](https://github.com/postgres/postgres/blob/master/contrib/btree_gist/btree_time.c), just to give you an idea of what you're getting into. – Nick Barnes Dec 22 '15 at 16:45
  • 1
    FWIW, the exclusion constraint doesn't seem like it should be necessary; I think that a constraint like this should (in theory) be implementable with a unique index and a custom B-tree opclass. But I [tried it](http://pastebin.com/KQ46UjKv), and it doesn't appear to work. I suspect that the treatment of nulls is hardwired into the indexing method. Not sure if a GiST opclass would be any different. – Nick Barnes Dec 22 '15 at 17:05

1 Answers1

2

You chose a real ordeal. Use a unique index, which is much simpler, safer and faster.

CREATE TABLE foo (
    foo_id serial PRIMARY KEY,
    foo text NOT NULL,
    bar timestamptz,
    baz timestamptz
);
CREATE TABLE

CREATE UNIQUE INDEX foo_foo_bar_baz_idx ON foo 
(foo, coalesce(bar, 'infinity'), coalesce(baz, 'infinity'));
CREATE INDEX

INSERT INTO foo VALUES
(default, '', null, null),
(default, '', now(), null),
(default, '', null, now());
INSERT 0 3

INSERT INTO foo VALUES
(default, '', null, null);
ERROR:  duplicate key value violates unique constraint "foo_foo_bar_baz_idx"
DETAIL:  Key (foo, (COALESCE(bar, 'infinity'::timestamp with time zone)), (COALESCE(baz, 'infinity'::timestamp with time zone)))=(, infinity, infinity) already exists.
klin
  • 112,967
  • 15
  • 204
  • 232
  • I'm trying to avoid using `coalesce()` in a functional index and stick to the `EXCLUDE` constraint; however, you say it's _simpler, safer and faster_ in your answer, but not much else... would you please explain the problems with the requested approach? – losthorse Dec 22 '15 at 03:42
  • 3
    `Exclude` creates a unique index. So why define your own operators, class of operators and type of index if a similar result can be achieved by using one statement? I see no reason to avoid `coalesce ()` as `IS DISTINCT` practically boils down to the same thing. Why safer? The less code, the less hassle. – klin Dec 22 '15 at 04:03
  • @losthorse Also, existing features are much more heavily tested than custom code. – jpmc26 Dec 22 '15 at 04:06
  • I agree with your reasoning (less code, less hassle), and @jpmc26 also makes a compelling point... I agree with you both, `coalesce()` may be the better choice (in this case); however, your response fails to answer the question asked... if you could expand your answer to include how to tie the custom operators to an index (perhaps with an example) then I will except it. – losthorse Dec 22 '15 at 14:23
  • You should [create operator class](http://www.postgresql.org/docs/9.1/static/sql-createopclass.html) for given data and index types. You have to be very careful however not to override existing default operator classes unless you defined all operators and functions expected in the index implementation. It is not a trivial task and you should have really important reason to try to deal with it. – klin Dec 22 '15 at 16:17