PostgreSQL 9.4 - Use custom operator in EXCLUDE constraint

Question

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.

The opclass documentation is pretty useless without this and this. The btree_gist extension's GiST implementation for timestamp is here and here, just to give you an idea of what you're getting into. — Nick Barnes
– Nick Barnes, Commented Dec 22, 2015 at 16:45
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, 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
– Nick Barnes, Commented Dec 22, 2015 at 17:05

klin · Accepted Answer · 2015-12-22 03:28:47Z

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.

answered Dec 22, 2015 at 3:28

klin

123k15 gold badges240 silver badges262 bronze badges

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

5 Comments

losthorse Over a year ago

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?

klin Over a year ago

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.

jpmc26 Over a year ago

@losthorse Also, existing features are much more heavily tested than custom code.

losthorse Over a year ago

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.

klin Over a year ago

You should create operator class 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.

Collectives™ on Stack Overflow

PostgreSQL 9.4 - Use custom operator in EXCLUDE constraint

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related