On Mon, Nov 14, 2022 at 2:55 AM Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 11/13/22 13:07, Tom Lane wrote: > > Adrian Klaver <adrian.kla...@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.kla...@aklaver.com > > > > INSERT INTO books VALUES (12, 0, CURRENT_TIMESTAMP) ON CONFLICT (id) WHERE version IS NULL OR updated + INTERVAL '2min' < CURRENT_TIMESTAMP DO UPDATE SET version = books.version + 1, updated = CURRENT_TIMESTAMP; Since id is already the primary key, it skipped the WHERE part. it resolves to the DO UPDATE part. from test code. > create table insertconflicttest(key int4, fruit text); > create unique index partial_key_index on insertconflicttest(key) where > fruit like '%berry'; > In this case, the on conflict clause should be exactly like *on conflict (key) where fruit like '%berry'* -- fails insert into insertconflicttest values (23, 'Blackberry') on conflict (key) do update set fruit = excluded.fruit; ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' or fruit = 'consequential' do nothing; ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit) where fruit like '%berry' do update set fruit = excluded.fruit; ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -- I recommend David Deutsch's <<The Beginning of Infinity>> Jian
