ON CONFLICT and WHERE - Mailing list pgsql-general

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.klaver@aklaver.com