Upsert总是更新postgresql，即使不匹配where子句

Question

我有sql查询:

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;

但是，即使where子句不为真，也会更新该行。下面是示例:https://dbfiddle.uk/CPHvZDm3

我不明白这是怎么回事。

Answer 1

WHERE子句的位置是问题所在。更正下面的陈述。

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;
select *, CURRENT_TIMESTAMP, updated + INTERVAL '2min' < CURRENT_TIMESTAMP from books where id = 12;
id | version |          updated           |       current_timestamp        | ?column? 
----+---------+----------------------------+--------------------------------+----------
12 |       0 | 11/13/2022 10:34:06.028222 | 11/13/2022 10:34:06.055526 PST | f

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;
select *, CURRENT_TIMESTAMP, updated + INTERVAL '2min' < CURRENT_TIMESTAMP from books where id = 12
id | version |          updated           |       current_timestamp        | ?column? 
----+---------+----------------------------+--------------------------------+----------
12 |       0 | 11/13/2022 10:34:06.028222 | 11/13/2022 10:34:08.668121 PST | f

From docs INSERT:

and conflict_action is one of:
DO NOTHING
DO UPDATE SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ] ( { expression |  DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
[ WHERE condition ]