Re: PostgreSQL domains and NOT NULL constraint

On 10/12/23 15:54, Tom Lane wrote:
> Erki Eessaar <erki(dot)eessaar(at)taltech(dot)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