Re: BUG #16150: UPDATE set NULL value in non-null columns

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: maximaximax(at)gmail(dot)com
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #16150: UPDATE set NULL value in non-null columns
Date: 2019-12-05 15:18:10
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs

PG Bug reporting form <noreply(at)postgresql(dot)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


In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2019-12-05 16:02:33 BUG #16153: foreign key update should probably move dependent rows in the case of tuple rerouting
Previous Message Scott Volkers 2019-12-05 14:54:10 Re: BUG #16148: Query on Large table hangs in ETL flows and gives out of memory when run in pgAdmin4