sqlpostgresqlindexingpsqlb-tree

Conditional index and trigger in postgres


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;

Solution

  • 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.