Upsert always updates postgreSQL

Question

I have sql query:

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;

however even if the where clause is not true, the row is updated. Here's example: https://dbfiddle.uk/CPHvZDm3

I can't understand what is wrong here.

Answer 1

According to the Postgres documentation https://www.postgresql.org/docs/current/sql-insert.html the INSERT INTO statement has two WHERE conditions:

1. ON CONFLICT ... WHERE index_predicate in the conflict_target part. Postgres needs to understand on what unique index it should react to conflicts, especially if you have multiple unique indexes on a table. You can specify the exact index name, or you can specify column name and other parameters and Postgres will try to find ("infer") corresponding index. The index_predicate allows to infer partial unique indexes (i.e. unique indexes that contain information only about part of the table), but any indexes that satisfy the predicate (not only partial indexes) can be inferred.
2. DO UPDATE SET ... WHERE condition in the conflict_action part. Only rows for which this condition expression returns true will be updated.

So you need to move you WHERE clause from the ON CONFLICT block to the DO UPDATE SET block:

CREATE TABLE books (
    id int4 NOT NULL,
    version int8 NOT NULL,
    updated timestamp NULL,
    CONSTRAINT books_pkey PRIMARY KEY (id)
);

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;

Upd: In your case the version cannot be NULL and you don't need the books.version IS NULL condition. In case you need to allow NULLs be aware that NULL + 1 will be NULL (thanks @dwhitemv for noticing) so you will need to modify the SET statement using COALESCE or conditions.

Answer 2

You need to move the WHERE clause to after the DO UPDATE:

INSERT INTO books VALUES (12, 0, CURRENT_TIMESTAMP)
ON CONFLICT (id) 
DO UPDATE SET version = books.version + 1, 
updated = CURRENT_TIMESTAMP 
WHERE version IS NULL OR updated + INTERVAL '2min' < CURRENT_TIMESTAMP;

That said, version will always be set to NULL if it is already NULL as NULL+1=NULL.