Hi all,
I'm struggling to work out the correct way to use a partial unique index in an upsert context.
Here's the context: I'm creating a partial unique index on a table:
tmp=# CREATE TABLE foo (a INT NOT NULL, b int NOT NULL, c TEXT, d BOOLEAN DEFAULT false);
CREATE TABLE
tmp=# CREATE UNIQUE INDEX foo_unique_true ON foo (a, b) WHERE d = true;
CREATE INDEX
tmp=# INSERT INTO foo (a, b, c, d) VALUES (1, 2, 'one', false);
INSERT 0 1
tmp=# INSERT INTO foo (a, b, c, d) VALUES (1, 2, 'two', false);
INSERT 0 1
tmp=# INSERT INTO foo (a, b, c, d) VALUES (1, 2, 'three', true);
INSERT 0 1
tmp=# INSERT INTO foo (a, b, c, d) VALUES (1, 2, 'four', true);
ERROR: duplicate key value violates unique constraint "foo_unique_true"
This is all working as expected.
What I want to do is alter my INSERT to be an upsert for the violation of the index constraint foo_unique_true. However, I cannot work out the syntax for doing this as the partial index is not a constraint, and my interpretation of index_expression and index_predicate in the grammar[1] don't seem to be working:
First, trying to upsert as if foo_unique_true was a constraint:
tmp=# INSERT INTO foo (a, b, c, d) VALUES (1, 2, 'four', true)
tmp-# ON CONFLICT ON CONSTRAINT "foo_unique_true"
tmp-# DO UPDATE SET c = 'four' WHERE foo.a = 1 AND foo.b = 2 AND foo.d = true;
ERROR: constraint "foo_unique_true" for table "foo" does not exist
Second, trying with index_expression and index_predicate:
tmp=# INSERT INTO foo (a, b, c, d) VALUES (1, 2, 'four', true)
tmp-# ON CONFLICT (a, b) WHERE d = true
tmp-# DO UPDATE SET c = 'four' WHERE foo.a = 1 AND foo.b = 2 AND foo.d = true;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
If anyone knows what I'm doing wrong and how to get this to work, or knows that this is not possible to achieve, I'm all ears.
Cheers,
Tim