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;
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 UNIQUE
s are necessary on available_combinations
:
UNIQUE(a,b,c)
allows multiple different c
for the same (a,b)
pair and different (a,b)
pairs for the same c
.UNIQUE(c)
and UNIQUE(a,b,c)
still allows multiple different c
for the same (a,b)
pair.UNIQUE(a,b)
and UNIQUE(a,b,c)
still allows multiple different different (a,b)
pairs for the same c
.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.