According to the Postgres documentation https://www.postgresql.org/docs/current/sql-insert.html the INSERT INTO statement has two WHERE conditions:
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.
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.