Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ouJiZ-0005FY-E3 for pgsql-general@arkaria.postgresql.org; Sun, 13 Nov 2022 20:36:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ouJhZ-0003zh-2z for pgsql-general@arkaria.postgresql.org; Sun, 13 Nov 2022 20:35:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ouJhX-0003zX-Gp for pgsql-general@lists.postgresql.org; Sun, 13 Nov 2022 20:35:36 +0000 Received: from out5-smtp.messagingengine.com ([66.111.4.29]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ouJhP-00042Q-Ps for pgsql-general@postgresql.org; Sun, 13 Nov 2022 20:35:34 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.nyi.internal (Postfix) with ESMTP id 656D85C00CE for ; Sun, 13 Nov 2022 15:35:26 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute2.internal (MEProxy); Sun, 13 Nov 2022 15:35:26 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:date:date:from:from :in-reply-to:message-id:mime-version:reply-to:sender:subject :subject:to:to; s=fm3; t=1668371726; x=1668458126; bh=SwlDuLo1o5 MMLcDBMKiDupZSBBncHBK/vUo9t2GJWrw=; b=bp2qJ5bR7AGWPciUfl5lb1Ydwf HeA0iPlIi+c24wzKgFMGWM+bbol5JreqF5d5X9m8cg/AgjePnFEe8f8khfacNQwW yaMPmJmdY7UcflrOCDEus/3C0tQrgvsBd+g/V0t2K7+gSniRhF2RSsixIke28yHx E40kQKfcd05nvxjdXPWvbiDxKGP2yc85XqZvJ/QLmMdKtZSMqOnWEjgNVcPdcksb lqqUGmpGU9Gkcss6QuDUCsXy1SCzmTV+TApGGl+3GGHu4YoV5dgmnZFOBXjZfAET A9mM0+ltrNg996pl2uvMBwiv+KTACIzZoasWHQs2C3kBQVWrM1OwMFn+mDPA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :date:date:feedback-id:feedback-id:from:from:in-reply-to :message-id:mime-version:reply-to:sender:subject:subject:to:to :x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm1; t=1668371726; x=1668458126; bh=SwlDuLo1o5MMLcDBMKiDupZSBBnc HBK/vUo9t2GJWrw=; b=PvbaBfBqqhSEm0Rhg7AHqwEfSmC8RYqbQ/Pmm4Zer1y6 1ipsEXKpiPNLOYDaCyYA3anyIlMWPiv/Jfh0gl2mGxLBe8lOr8UaFFJTqwXakESU SAhvCHrOKCcg3PrUO/QLy6uONJqCMoTcwHoTULxYOUClHijZZ7AnPe7FzJMMEjMF 4qXOocnlwNiBqfqpux14+GllzRWs9hVIPnQoLpoyjayi9Hs3xjUXWb0o50TZFV9U wVS8pTpCnvXcZV8zCNRANovkUl8iJXuIrErE57tC4MuZReWF4keOmd93O51kMp2Q F1J0qXWgE6WWd5y/dMXnClH6YElMZnj04CmUZE7ItA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvgedrgedtgddugeduucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpefkffggfgfvhffutgfgsehtjeertd dtfeejnecuhfhrohhmpeetughrihgrnhcumfhlrghvvghruceorggurhhirghnrdhklhgr vhgvrhesrghklhgrvhgvrhdrtghomheqnecuggftrfgrthhtvghrnhepjeeggfejkeejve evtdekvdduffevheejteejtefgffdvffevtddvkeehffetgfefnecuvehluhhsthgvrhfu ihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhirghnrdhklhgrvhgvrh esrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA for ; Sun, 13 Nov 2022 15:35:25 -0500 (EST) Message-ID: <4cc58ea7-c69d-cb4a-de8a-da66d79f0f5a@aklaver.com> Date: Sun, 13 Nov 2022 12:35:25 -0800 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Content-Language: en-US To: pgsql-general From: Adrian Klaver Subject: ON CONFLICT and WHERE Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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