Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
710 views
in Technique[技术] by (71.8m points)

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

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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_active
DO UPDATE
SET value = '33', is_active = true;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...