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/
