CODEWITHSUNDEEP
postgresqlpostgresql-9.4
losthorse
losthorse

Reputation: 1570

PostgreSQL 9.4 - Use custom operator in EXCLUDE constraint

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.

Upvotes: 5

Views: 1125

Answers (1)

klin
klin

Reputation: 121594

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.

Upvotes: 2

Related Questions