Constraint to only allow unique value in a group
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 &lt;&gt; 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();