pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: maximaximax@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16150: UPDATE set NULL value in non-null columns
Date: Thu, 05 Dec 2019 10:18:10 -0500
Message-ID: <13951.1575559090@sss.pgh.pa.us> (raw)
In-Reply-To: <16150-dc6886080d71e985@postgresql.org>
References: <16150-dc6886080d71e985@postgresql.org>

PG Bug reporting form <noreply@postgresql.org> 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





view thread (3+ messages)

Message-ID: <13951.1575559090@sss.pgh.pa.us>
Permalink:  ../13951.1575559090@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/13951.1575559090@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, maximaximax@gmail.com
  Subject: Re: BUG #16150: UPDATE set NULL value in non-null columns
  In-Reply-To: <13951.1575559090@sss.pgh.pa.us>
  References: <16150-dc6886080d71e985@postgresql.org> <13951.1575559090@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox