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