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 1kcD6e-0005SC-9F for pgsql-bugs@arkaria.postgresql.org; Mon, 09 Nov 2020 19:45:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kcD6d-0007PY-0K for pgsql-bugs@arkaria.postgresql.org; Mon, 09 Nov 2020 19:45:35 +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 1kcD6c-0007PR-Qh for pgsql-bugs@lists.postgresql.org; Mon, 09 Nov 2020 19:45:34 +0000 Received: from relay6-d.mail.gandi.net ([217.70.183.198]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kcD6M-0007pK-9a for pgsql-bugs@lists.postgresql.org; Mon, 09 Nov 2020 19:45:33 +0000 X-Originating-IP: 79.88.149.14 Received: from [172.16.255.146] (14.149.88.79.rev.sfr.net [79.88.149.14]) (Authenticated sender: vik@postgresfriends.org) by relay6-d.mail.gandi.net (Postfix) with ESMTPSA id 1F781C0005; Mon, 9 Nov 2020 19:45:13 +0000 (UTC) Subject: Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint To: Tom Lane , zhongxuchen@gmail.com Cc: pgsql-bugs@lists.postgresql.org References: <16706-08837c0cdaf4ce57@postgresql.org> <1974489.1604935011@sss.pgh.pa.us> From: Vik Fearing Message-ID: Date: Mon, 9 Nov 2020 20:45:13 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 In-Reply-To: <1974489.1604935011@sss.pgh.pa.us> Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 11/9/20 4:16 PM, Tom Lane wrote: > PG Bug reporting form writes: >> example: >> CREATE TABLE TEST1 ( >> id varchar(100) NOT NULL, >> name varchar(100) NOT NULL, >> status varchar(100) NOT NULL, >> CONSTRAINT test1_pkey PRIMARY KEY (id) >> ) > >> insert into TEST1 (ID, NAME,STATUS ) values('4','test','6') ; >> insert into TEST1 as t1 (ID, NAME,STATUS ) values('4',null,'6') on conflict >> (id) do update set NAME=COALESCE(excluded.NAME,t1.NAME), >> STATUS=COALESCE(excluded.STATUS,t1.STATUS); > > I get > > ERROR: null value in column "name" violates not-null constraint > DETAIL: Failing row contains (4, null, 6). That's the complaint. > so this seems to have been fixed already. 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. -- Vik Fearing