I have the following Entity Attribute value table :
CREATE TABLE key_value_pair (
id serial NOT NULL PRIMARY KEY,
key varchar(255) NOT NULL,
value varchar(255),
is_active boolean
);
CREATE UNIQUE INDEX key_value_pair_key_if_is_active_true_unique ON key_value_pair (key) WHERE is_active = true;
Sample entries in this table are :
id | key | value | is_active
----+-------------+-------+-----------
1 | temperature | 2 | f
2 | temperature | 12 | f
3 | temperature | 15 | f
4 | temperature | 19 | f
5 | temperature | 23 | t
(5 rows)
Thus, at any point in time, for any given key, only 1 true is_active entry should be present.
I am running the following upsert statement on this table :
INSERT INTO key_value_pair (key, value, is_active) VALUES ('temperature','20', true)
ON CONFLICT (key, is_active)
DO UPDATE
SET value = '33', is_active = true;
But, it fails with:
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
What I am wondering is why it is not using the unique partial index key_value_pair_key_if_is_active_true_unique.
The upsert works if I let go of the "at any point in time, for any given key, only 1 true is_active entry should be present" clause and change the index to:
CREATE UNIQUE INDEX key_value_pair_key_if_is_active_true_unique ON key_value_pair (key, is_active);
I read documentation on the Postgres website that partial indexes will be used by the ON CONFLICT clause. I wonder why it is not used in this case. What am I missing here, or what error am I making?