On 2025-Aug-14, Kirill Reshke wrote:
> reshke=# create domain dd as int;
> CREATE DOMAIN
> reshke=# create table dt(i int, c dd);
> CREATE TABLE
> reshke=# insert into dt values(1,null);
> INSERT 0 1
> reshke=# alter domain dd add constraint c not null not valid ;
> ALTER DOMAIN
> reshke=# update dt set i = i + 1;
> UPDATE 1
I think what this example is saying, is that for a not-null constraint
on a domain to be really useful, it has to be propagated as a not-null
constraint on all table (and matview) columns that are using that domain
as datatype. In this case the table column remains nullable after
adding the constraint to the domain, which is why no error occurs (and
which IMO is bogus). In your other update
> reshke=# update dt set i = i + 1, c =null;
> ERROR: domain dd does not allow null values
the error occurs not when the null value is inserted into the column,
but when the value is assigned the domain type.
The 2016 SQL standard says in 4.23.4 Domain Constraints:
A domain constraint is a constraint that is specified for a domain. It
is applied to all columns that are based on that domain, and to all
values cast to that domain.
which supports the idea that have should do this propagation that I
describe.
So what I think should happen here, is that if you do
ALTER DOMAIN foo ADD CONSTRAINT NOT NULL
then we would look up all columns in all tables/matviews that have a
column of that datatype, and create another CONSTRAINT_NOTNULL
pg_constraint row for that column of that table; then verify all
tables/matviews [that don't already have not-null constraints on those
columns] and error out if there's a null value in any of them.
Contrariwise, and most usefully, if you do
ALTER DOMAIN ADD CONSTRAINT NOT NULL NOT VALID
then you add all the constraints on each table column and mark them as
not-valid; no need for any error here. Then the user can validate each
table separately if they want, minimizing time during which tables are
locked.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
