Conditional index and trigger in postgres

Question

Trying to create a conditional unique index in postgresql but unable to do so and getting this error

Query 1 ERROR: ERROR:  cannot use subquery in index predicate
LINE 3: WHERE (
              ^

this is how my query looks like

CREATE UNIQUE INDEX conditional_unique_index
ON test_table (a, b)
WHERE (
    SELECT COUNT(*)
    FROM test_table t2
    WHERE t2.a = test_table.a AND t2.b = test_table.b
) = 1;

My requirement is this:

CREATE TABLE test_table (
    a integer,
    b integer,
    c integer
);
INSERT INTO test_table (a, b, c) VALUES
(1, 2, 22),
(1, 2, 22),
(1, 2, 22),
(1, 3, 34),
(2, 3, 26),
(2, 3, 26);

the conditions are if there are multiple rows with the same values in columns (a, b) then in the column c values must be the same like this (1, 2, 22),(1, 2, 22),(1, 2, 22); and this is not allowed (1, 2, 22),(1, 2, 23),(1, 2, 22); c must be same here (1, 2, 23)

now the second condition is if columns (a, b) are unique then c also should be unique like this (1, 3, 34),(2, 3, 26); and this is not allowed (1, 3, 34),(2, 3, 34); same values for c is not allowed in this case

so far for this 1st case, I have managed it by using triggers

CREATE OR REPLACE FUNCTION check_conditional_unique() RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM test_table t2
        WHERE t2.a = NEW.a AND t2.b = NEW.b AND t2.c <> NEW.c
    ) THEN
        RAISE EXCEPTION 'Duplicate values in c for the same a and b combination.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_check_conditional_unique
BEFORE INSERT ON test_table
FOR EACH ROW
EXECUTE FUNCTION check_conditional_unique();

but for this 2nd case I'm facing issue this queury is wrong what i have wrote for this

CREATE UNIQUE INDEX conditional_unique_index
ON test_table (a, b)
WHERE (
    SELECT COUNT(*)
    FROM test_table t2
    WHERE t2.a = test_table.a AND t2.b = test_table.b
) = 1;

Answer 1

You can handle both cases you described, in your trigger, without the constraint: demo

CREATE OR REPLACE FUNCTION check_conditional_unique() RETURNS TRIGGER AS $f$
BEGIN
    IF EXISTS (--reject same (a,b) for different c
        SELECT FROM test_table t2
        WHERE (t2.a,t2.b) = (NEW.a,NEW.b) 
        AND    t2.c       <> NEW.c
    ) THEN
        RAISE EXCEPTION 'New values in c for the same a and b combination.';
    END IF;
    IF EXISTS (--reject same c for different (a,b)
        SELECT FROM test_table t2
        WHERE (t2.a,t2.b) <> (NEW.a,NEW.b) 
        AND    t2.c       =   NEW.c
    ) THEN
        RAISE EXCEPTION 'Duplicate values in c for different a and b combination.';
    END IF;
    RETURN NEW;
END;
$f$ LANGUAGE plpgsql;

The examples you gave don't match what unique or partial unique constraints are for; as long as they share a common c you do need duplicate pairs of (a,b) to be allowed.

---

As suggested by @Laurenz Albe, to avoid race conditions you could split the structure into a catalogue of combinations to be referenced by your table, and apply your constraints at the catalogue level. To fully cover your rules: (demo)

CREATE TABLE available_combinations (
   a integer NOT NULL,
   b integer NOT NULL,
   c integer NOT NULL UNIQUE,
   UNIQUE(a,b),
   UNIQUE(a,b,c)--this is a natural consequence of the previous two UNIQUEs but
                --it's required for foreign keys to link complete combinations
);

CREATE TABLE test_table (
   pkey integer generated by default as identity PRIMARY KEY,
   a integer NOT NULL,
   b integer NOT NULL,
   c integer NOT NULL,
   FOREIGN KEY (a, b, c) REFERENCES available_combinations (a, b, c)
);

The test_table allows for multiple references to the same combination, thus allowing the valid repetitions your described.

All three UNIQUEs are necessary on available_combinations:

1. Leaving only UNIQUE(a,b,c) allows multiple different c for the same (a,b) pair and different (a,b) pairs for the same c.
2. Leaving only UNIQUE(c) and UNIQUE(a,b,c) still allows multiple different c for the same (a,b) pair.
3. Leaving only UNIQUE(a,b) and UNIQUE(a,b,c) still allows multiple different different (a,b) pairs for the same c.
4. It's actually ok to leave only UNIQUE(a,b) and UNIQUE(c). It already logically implies UNIQUE(a,b,c) as well, but the full constraint is required to force foreign records to reference complete combinations.

You can also leave either c or (a,b) completely off test_table which allows you to limit the foreign key specification to only one of these and remove UNIQUE(a,b,c), but it also forces you to grab it through a non-insertable view or join it back on every select. Demo:

CREATE TABLE available_combinations (
   a integer NOT NULL,
   b integer NOT NULL,
   c integer NOT NULL UNIQUE,
   UNIQUE(a,b)                      );

CREATE TABLE test_table (
   pkey integer generated by default as identity PRIMARY KEY,
   c integer REFERENCES available_combinations (c) NOT NULL  );

CREATE VIEW v_test_table AS SELECT pkey,a,b,c
FROM test_table t NATURAL JOIN available_combinations a;

You could consider a trigger that creates a new available_option when you insert one into test_table without creating it first.

Answer 2

The classical solution for this is no normalize the data model by splitting it into several tables. Observe that c is functionally dependent on (a, b). So you could devise one table like this:

CREATE TABLE tab2 (
   a integer NOT NULL,
   b integer NOT NULL,
   c integer NOT NULL,
   PRIMARY KEY (a, b)
);

Since a and b can occur multiple times in your setup, you then have another table like this:

CREATE TABLE tab1 (
   pkey integer PRIMARY KEY,
   a integer NOT NULL,
   b integer NOT NULL,
   FOREIGN KEY (a, b) REFERENCES tab2 (a, b)
);

The primary key was added because every table should/must have one.

Your table then becomes a view:

CREATE VIEW test_table AS
SELECT tab1.a, tab1.b, tab2.c
FROM tab1 JOIN tab2 USING (a, b);