On 10/12/23 15:54, Tom Lane wrote:
Erki Eessaar <erki.eess...@taltech.ee> writes:
PostgreSQL's CREATE DOMAIN documentation (section Notes) describes a way how
one can add NULL's to a column that has a domain with the NOT NULL constraint.
https://www.postgresql.org/docs/current/sql-createdomain.html
To me it seems very strange and amounts to a bug because it defeats the purpose
of domains (to be a reusable assets) and constraints (to avoid any bypassing of
these).
I doubt we'd consider doing anything about that. The whole business
of domains with NOT NULL constraints is arguably a defect of the SQL
standard, because there are multiple ways to produce a value that
is NULL and yet must be considered to be of the domain type.
The subselect-with-no-output case that you show isn't even the most
common one; I'd say that outer joins where there are domain columns
on the nullable side are the biggest problem.
There's been some discussion of treating the output of such a join,
subselect, etc as being of the domain's base type not the domain
proper. That'd solve this particular issue since then we'd decide
we have to cast the base type back up to the domain type (and hence
check its constraints) before inserting the row. But that choice
just moves the surprise factor somewhere else, in that queries that
used to produce one data type now produce another one. There are
applications that this would break. Moreover, I do not think there's
any justification for it in the SQL spec.
I do not believe this is a defect of the SQL standard at all.
SQL:2023-2 Section 4.14 "Domains" clearly states "The purpose of a
domain is to constrain the set of valid values that can be stored in a
column of a base table by various operations."
That seems very clear to me that *storing* a value in a base table must
respect the domain's constraints, even if *operations* on those values
might not respect all of the domain's constraints.
Whether or not it is practical to implement that is a different story,
but allowing the null value to be stored in a column of a base table
whose domain specifies NOT NULL is frankly a bug.
--
Vik Fearing
