sqlpostgresqlcheck-constraintsexclude-constraint

Postgresql Constraint on row value based on other rows

Given the following schema

CREATE TABLE test (
    value text,
    flag bool
);

is this possible, to create such constraint which would allow duplicate rows with the same value and flag = true, but would allow at most once row with the given value and flag = false

e.g.

These should execute without errors

INSERT INTO test (value, flag) VALUES ('1', true);
INSERT INTO test (value, flag) VALUES ('1', true);
INSERT INTO test (value, flag) VALUES ('1', true);
INSERT INTO test (value, flag) VALUES ('1', true);

INSERT INTO test (value, flag) VALUES ('1', true);
INSERT INTO test (value, flag) VALUES ('1', true);
INSERT INTO test (value, flag) VALUES ('1', false);

INSERT INTO test (value, flag) VALUES ('1', false);
INSERT INTO test (value, flag) VALUES ('1', true);

And this should raise an error

INSERT INTO test (value, flag) VALUES ('1', false);
INSERT INTO test (value, flag) VALUES ('1', false);

I have tried playing with EXCLUDE constraint but was unable to make it work.

Solution

  • You're looking for a unique partial index:

    create unique index no_false_duplicates on test (value, flag) where not flag

    You'll have to figure out what you want to do about null values in flag of course. I'd recommend making the flag column not null since null booleans are rarely what you want.

    See the CREATE INDEX documentation for details.