ON CONFLICT and WHERE

[Adrian Klaver]

In process of answering an SO question I ran across the below.
The original question example:

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)
WHERE version IS NULL OR updated + INTERVAL '2min' < CURRENT_TIMESTAMP
DO UPDATE
SET version = books.version + 1, updated = CURRENT_TIMESTAMP;

select *, CURRENT_TIMESTAMP, updated + INTERVAL '2min' < 
CURRENT_TIMESTAMP from books where id = 12;

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;

select *, CURRENT_TIMESTAMP, updated + INTERVAL '2min' < 
CURRENT_TIMESTAMP from books where id = 12;

With select results as:

 id | version |          updated           |       current_timestamp 
    | ?column?
----+---------+----------------------------+--------------------------------+----------
 12 |       0 | 11/13/2022 12:21:38.032578 | 11/13/2022 12:21:38.057545 
PST | f


 id | version |          updated           |       current_timestamp 
    | ?column?
----+---------+----------------------------+--------------------------------+----------
 12 |       1 | 11/13/2022 12:21:38.058673 | 11/13/2022 12:21:40.686231 
PST | f


I have not used WHERE with ON CONFLICT myself so it took longer then I 
care to admit to correct the above to:

DROP TABLE IF EXISTS books;

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;

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


With select results as:

 id | version |          updated           |       current_timestamp 
    | ?column?
----+---------+----------------------------+--------------------------------+----------
 12 |       0 | 11/13/2022 12:32:01.427769 | 11/13/2022 12:32:01.463705 
PST | f


id | version |          updated           |       current_timestamp 
  | ?column?
----+---------+----------------------------+--------------------------------+----------
 12 |       0 | 11/13/2022 12:32:01.427769 | 11/13/2022 12:32:01.476484 
PST | f



I ran this on both version 14 and 15 with same results.

The question is why did the first case just ignore the WHERE instead of 
throwing a syntax error?


--
Adrian Klaver
adrian.kla...@aklaver.com