Re: ON CONFLICT and WHERE - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: ON CONFLICT and WHERE |
Date | |
Msg-id | 231bbc6b-3e01-9ce7-3560-0025e2361d16@aklaver.com Whole thread Raw |
In response to | Re: ON CONFLICT and WHERE (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: ON CONFLICT and WHERE
(jian he <jian.universality@gmail.com>)
|
List | pgsql-general |
On 11/13/22 13:07, Tom Lane wrote: > Adrian Klaver <adrian.klaver@aklaver.com> writes: >> INSERT INTO books VALUES (12, 0, CURRENT_TIMESTAMP) >> ON CONFLICT (id) >> WHERE updated IS NULL OR updated + INTERVAL '2min' < CURRENT_TIMESTAMP >> DO UPDATE >> SET version = books.version + 1, updated = CURRENT_TIMESTAMP; > >> I have not used WHERE with ON CONFLICT myself so it took longer then I >> care to admit to correct the above to: > >> INSERT INTO books VALUES (12, 0, CURRENT_TIMESTAMP) >> ON CONFLICT (id) >> DO UPDATE >> SET version = books.version + 1, updated = CURRENT_TIMESTAMP >> WHERE books.version IS NULL OR books.updated + INTERVAL '2min' < >> CURRENT_TIMESTAMP; > >> The question is why did the first case just ignore the WHERE instead of >> throwing a syntax error? > > A WHERE placed there is an index_predicate attachment to the ON CONFLICT > clause. It doesn't have any run-time effect other than to allow partial > indexes to be chosen as arbiter indexes. TFM explains > > 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. > > This strikes me as a bit of a foot-gun. I wonder if we should make > it safer by insisting that the resolved index be partial when there's > a WHERE clause here. (This documentation text is about as clear as > mud, too. What does "inferred" mean here? I think it means "chosen as > arbiter index", but maybe I misunderstand.) Alright I see how another use of WHERE comes into play. I do agree with the clarity of the description, especially after looking at the example: " Insert new distributor if possible; otherwise DO NOTHING. Example assumes a unique index has been defined that constrains values appearing in the did column on a subset of rows where the is_active Boolean column evaluates to true: -- This statement could infer a partial unique index on "did" -- with a predicate of "WHERE is_active", but it could also -- just use a regular unique constraint on "did" INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International') ON CONFLICT (did) WHERE is_active DO NOTHING; " I honestly cannot figure out what that is saying. > > regards, tom lane -- Adrian Klaver adrian.klaver@aklaver.com
pgsql-general by date: