2

I have a table in PostgreSQL 12 with columns "sample", "group", "value". There can be multiple samples in a group. Is it possible to implement a constraint so that each group can only have one distinct value?

EDIT: This is for a rather complex materialized view, where I would like to make sure that there are no samples in a group that has a value that differ from the other samples. In that sense "value" should be a property of the group in this specific context, but that isn't always the case in the database. The use case is that samples are biological samples taken from different tissues ("value") and groups are e.g. "treated" and "control", and I would like to make sure that there aren't a mix of tissues within one group.

This should be ok: ('sample1', 'group1', 'value1'), ('sample2', 'group1', 'value1'), 'sample3', 'group2', 'value2'). Here there are two samples in group 1, but they both have the same value.

This should not be ok: ('sample1', 'group1', 'value1'), ('sample2', 'group1', 'value2'), 'sample3', 'group2', 'value2')

This should fail because the two samples in group 1 have different values.

I think some of the confusion came from that I used "unique" in the original post when I was talking about distinct values, but this has nothing to do with UNIQUE. For this to work a CHECK constraint for a row being added would depend on several other existing rows. I don't know if that works? Or if there should be a trigger maybe? I guess there could be an issue with race conditions on bulk inserts?

14
  • What exactly is "one unique value", is ('group1', 1), ('group1', 2) OK?. If yes this sounds like a standard unique index on ("group", value)
    – user1822
    Commented Jun 8, 2020 at 12:52
  • No, COUNT DISTINCT(group, value) = 1.
    – Rasmus
    Commented Jun 8, 2020 at 13:00
  • 1
    That's a unique index on ("group", value)
    – user1822
    Commented Jun 8, 2020 at 13:08
  • Oh :) I thought that would mean that there could be only one row for each combination of "group" and "sample". Thanks a lot then!
    – Rasmus
    Commented Jun 8, 2020 at 13:13
  • Yes, that's what it does, and that's what your "count" example does as well
    – user1822
    Commented Jun 8, 2020 at 13:14

1 Answer 1

3

CHECK constraints cannot use subqueries, so you have to use a trigger:

CREATE FUNCTION check_one_value_per_group() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF EXISTS (SELECT * FROM my_table
               WHERE "group" = NEW."group"
                 AND value <> NEW.value;
              ) THEN
        RAISE EXCEPTION 'different values in group %', NEW."group";
    END IF;
    RETURN NEW;
END;
$$;

CREATE CONSTRAINT TRIGGER one_value_per_group
AFTER INSERT OR UPDATE OF "group", value ON my_table
FOR EACH ROW
EXECUTE FUNCTION check_one_value_per_group();
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.