Re: PostgreSQL domains and NOT NULL constraint

On 10/12/23 15:54, Tom Lane wrote:
> Erki Eessaar <erki.eessaar@taltech.ee> writes:
>> PostgreSQL's CREATE DOMAIN documentation (section Notes) describes a way how one can add NULL's to a column that has
adomain 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)
andconstraints (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