Re: PostgreSQL domains and NOT NULL constraint

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.

Our general opinion about this is what is stated in the NOTES
section of our CREATE DOMAIN reference page [1]:

Best practice therefore is to design a domain's constraints so that a
null value is allowed, and then to apply column NOT NULL constraints
to columns of the domain type as needed, rather than directly to the
domain type.

regards, tom lane

[1] https://www.postgresql.org/docs/current/sql-createdomain.html