Question 261488 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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;
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search