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 1kg6iA-0004N6-HS for pgsql-bugs@arkaria.postgresql.org; Fri, 20 Nov 2020 13:44:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kg6i9-0004FZ-8p for pgsql-bugs@arkaria.postgresql.org; Fri, 20 Nov 2020 13:44:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kg6i9-0004FS-2z for pgsql-bugs@lists.postgresql.org; Fri, 20 Nov 2020 13:44:25 +0000 Received: from lahtoruutu.iki.fi ([2a0b:5c81:1c1::37]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kg6i6-00052H-4j for pgsql-bugs@lists.postgresql.org; Fri, 20 Nov 2020 13:44:24 +0000 Received: from [192.168.1.113] (dsl-hkibng22-54faa4-119.dhcp.inet.fi [84.250.164.119]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) (Authenticated sender: hlinnaka) by lahtoruutu.iki.fi (Postfix) with ESMTPSA id 34A361B00121; Fri, 20 Nov 2020 15:44:20 +0200 (EET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=iki.fi; s=lahtoruutu; t=1605879860; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=Bosd7xaWWuc/Z9cCTaB/aHoWU1bij7cpxXoAUrfm7G0=; b=kPDkkX5grTjY3sfKWuBnjkgyUPTsiNP1je4b3zHpx0H1d0H3mtRuL03p2YAIIw3ehhsFru I5r/67Vh9/LCS9zQPRAHQ0F0NvQFxiJcJwgoCS3fs5qfFec5h7whfBvdow3C22pllZmqc6 XdCqHbhZ5r7dGU1rzGMopP+2PlmWMNmthGAF4ffiLYPRLBZ5G1XQWaqPAPocpkQvDoznz7 0ULCjL0bDe2n+/tdpU8J/GprHnOA1//unNiaZH94egHRslBOvkx0W701/Ss+l6uIPxZNd/ pTR5Owkv2cw9gic9/iitnq8spoYHhPSMUXs67vDDOkQdCJ+NJpgdyNr0TcVjwQ== Subject: Re: BUG #16733: insert into on conflict(pk) do nothing error violates not-null constraint To: zhongxuchen@gmail.com, pgsql-bugs@lists.postgresql.org, PG Bug reporting form References: <16733-fb8b26e66710b2a1@postgresql.org> From: Heikki Linnakangas Message-ID: <382dda8f-c8d3-6f4d-ea74-95a87df3f3c1@iki.fi> Date: Fri, 20 Nov 2020 15:44:19 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.4.0 MIME-Version: 1.0 In-Reply-To: <16733-fb8b26e66710b2a1@postgresql.org> Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-US Content-Transfer-Encoding: 8bit ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=iki.fi; s=lahtoruutu; t=1605879860; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=Bosd7xaWWuc/Z9cCTaB/aHoWU1bij7cpxXoAUrfm7G0=; b=PxvhnsOr6ZgMWq6K3LxilGscjgGCg0bJwYo6vcGERBAN9U1bTb0RGdTjZrr3OoCvA4V7Pt 2GDMmux0EJ8E/w86KxiLHz9zTENT33GAr6lrRS3RPRBgxNxp/jRjds7DPHTXXKnk6vzTGl 1kA3IKs5MQM0T7FUzBRZLJ9ZJ3sV9vk2mPDQNvfqtW00BDcXkSBqGtO1fKaGkTppLUARBs xs6ckTQwxKs+oh6DLlsCG2Kc1PHcYKQ4hIJK7BWnO5UA3yQVzR4uqb+jKHuvCgpm/W3iWu f2e2AA+2sGKY7lGLcOJOsT0FZLFjDtegVfvaiQOXuu3yoD1COccA2ajVIIH/Rg== ARC-Authentication-Results: i=1; ORIGINATING; auth=pass smtp.auth=hlinnaka smtp.mailfrom=hlinnaka@iki.fi ARC-Seal: i=1; s=lahtoruutu; d=iki.fi; t=1605879860; a=rsa-sha256; cv=none; b=JDyFiOe48iKnu5vYkC3OwKHm40A2aIWkE51wrkkAuYsMxTl/42rBSR1koJFwW/jtZmYgr8 fxO44bRwfDPpNFnEVWmQF3kjiFdAmAbwzl4g9+egw8pj7J2Fef1gA6uXJ8QY0WabVNg7u6 l7SuWqrO2+lGYUgtvns0XpbWsn3pad0NlgxsbbJg2BFmWRN68wnvNCc83P9Bo5XBObbxcm +qFCGuADHquPO7NciiA/fSejIfLiaHkPRlCq+9BkLUHRWnPYEDjcRFakMprVtdkne6jNnm 5kCeDAkl7b+ka7ZN2VTGDddArsJQNgoaohNnXiA6LopVUmLVu3V33I7T8pV7bg== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 20/11/2020 07:39, PG Bug reporting form wrote: > The following bug has been logged on the website: > > Bug reference: 16733 > Logged by: zhongxuchen > Email address: zhongxuchen@gmail.com > PostgreSQL version: 12.3 > Operating system: centos8 > Description: > > sorry ,A scene that was missed before! For the archives: I believe you're referring to https://www.postgresql.org/message-id/16706-08837c0cdaf4ce57%40postgresql.org. > 1、postgresql insert()values()on conflict (pk) do update/ do nothing > 当某个字段在数据库中设置为not null,SQL 错误 [23502]: ERROR: null value in column "name" > violates not-null constraint > > 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) > ) > > Initialization data > insert into TEST1 (ID, NAME,STATUS ) values('4','test','6') ; > > Error scenarios: > 1、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); > > 2、insert into TEST1 as t1 (ID, NAME,STATUS ) values('4',null,'6') on > conflict > (id) do nothing Both of these fail with an error: ERROR: null value in column "name" of relation "test1" violates not-null constraint DETAIL: Failing row contains (4, null, 6). This is not a bug. The NOT NULL constraint is checked before checking for conflicts. - Heikki