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 1kcDZC-0006cn-MS for pgsql-bugs@arkaria.postgresql.org; Mon, 09 Nov 2020 20:15:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kcDZB-0006GP-Ji for pgsql-bugs@arkaria.postgresql.org; Mon, 09 Nov 2020 20:15:05 +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 1kcDZB-0006GH-D3 for pgsql-bugs@lists.postgresql.org; Mon, 09 Nov 2020 20:15:05 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kcDZ9-0008Ah-4R for pgsql-bugs@lists.postgresql.org; Mon, 09 Nov 2020 20:15:04 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 0A9KF10O1996439; Mon, 9 Nov 2020 15:15:01 -0500 From: Tom Lane To: Vik Fearing cc: zhongxuchen@gmail.com, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint In-reply-to: References: <16706-08837c0cdaf4ce57@postgresql.org> <1974489.1604935011@sss.pgh.pa.us> Comments: In-reply-to Vik Fearing message dated "Mon, 09 Nov 2020 20:45:13 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1996437.1604952901.1@sss.pgh.pa.us> Date: Mon, 09 Nov 2020 15:15:01 -0500 Message-ID: <1996438.1604952901@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Vik Fearing writes: > On 11/9/20 4:16 PM, Tom Lane wrote: >> I get >> ERROR: null value in column "name" violates not-null constraint >> DETAIL: Failing row contains (4, null, 6). > That's the complaint. Oh ... it wasn't expressed very well then. I thought the OP was reporting that the not-null constraint wasn't being enforced, which surely *would* be a bug. > I think the issue is the not null constraint is evaluated before the ON > CONFLICT and so there is no chance to "heal" the row by coalescing the > old and new values. Ah. Well, ON CONFLICT is for resolving duplicate-key errors; it's not a get-out-of-jail-free card for every sort of error. I think the right way to handle this example would be with a before-update trigger, which IIRC can modify the row before we apply any table constraint checks. regards, tom lane