Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1icstV-0004GS-5T for pgsql-bugs@arkaria.postgresql.org; Thu, 05 Dec 2019 15:18:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1icstT-0002S7-Uh for pgsql-bugs@arkaria.postgresql.org; Thu, 05 Dec 2019 15:18:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1icstT-0002Rn-Mi for pgsql-bugs@lists.postgresql.org; Thu, 05 Dec 2019 15:18:15 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1icstQ-0007mU-QS for pgsql-bugs@lists.postgresql.org; Thu, 05 Dec 2019 15:18:15 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id xB5FIAAx013952; Thu, 5 Dec 2019 10:18:10 -0500 From: Tom Lane To: maximaximax@gmail.com cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16150: UPDATE set NULL value in non-null columns In-reply-to: <16150-dc6886080d71e985@postgresql.org> References: <16150-dc6886080d71e985@postgresql.org> Comments: In-reply-to PG Bug reporting form message dated "Thu, 05 Dec 2019 09:17:34 +0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <13950.1575559090.1@sss.pgh.pa.us> Date: Thu, 05 Dec 2019 10:18:10 -0500 Message-ID: <13951.1575559090@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk PG Bug reporting form writes: > Today I found a big problem, I can write a valid UPDATE which put NULL value > in non-null column. You have rediscovered the scenario explained in the "Notes" section of the CREATE DOMAIN reference page [1]. As stated there, we consider that defining a domain with a NOT NULL constraint (or a CHECK that will fail for nulls) is a bad idea, so we're not that excited about trying to bend the datatype semantics to the extent that would be needed to make this problem go away. There are only two possible fixes: (1) throw an error if a sub-select or outer join produces a null in a column of such a domain type, or (2) regard the output column of such a query as not being of the domain type anymore, but its base type. Neither of these are attractive, either from a functionality or performance standpoint. So it's hard to conclude anything except that the SQL committee didn't think this through very well. regards, tom lane [1] https://www.postgresql.org/docs/current/sql-createdomain.html