Partial Index not used in ON CONFLICT clause while performing an upsert in Postgresql

You have to use an index predicate to use a partial unique index. Read in the documentation:

index_predicate

Used to allow inference of partial unique indexes. Any indexes that satisfy the predicate (which need not actually be partial indexes) can be inferred. Follows CREATE INDEX format.

In this case:

INSERT INTO key_value_pair (key, value, is_active) VALUES ('temperature','20', false) ON CONFLICT (key) WHERE is_activeDO UPDATESET value = '33', is_active = true;

Another example:

> usersid |  name   |  colour  |  active---+---------+----------+--------1  | 'greg'  |  'blue'  |  false2  | 'kobus' |  'pink'  |  true--------------------------------------------------------------------------CREATE UNIQUE INDEX index_name  --columns applicable to partial index  ON users (name, colour)  --partial index condition   **  WHERE not active--------------------------------------------------------------------------INSERT INTO users (name, colour, active)    --multiple insertsVALUES ('greg', 'blue', false),  --this already exists for [false], conflict       ('pieter', 'blue', true),         ('kobus', 'pink', false)  --this already exists for [true], no conflictON CONFLICT (name, colour)    --partial index condition  **same as original partial index conditionWHERE not active    --conflict action  DO UPDATE SET        active = not EXCLUDED.active        --on conflict example, change some value, i.e. update [active]

Row where name = greg and colour = blue for active = false will now be updated to active = true, the rest will be inserted