Hi. Similar to https://git.postgresql.org/cgit/postgresql.git/commit/?id=96e2af605043974137d84edf5c0a24561956919e We apply this logic to the domain not-null constraint too. It would error out if ALTER DOMAIN ADD CONSTRAINT NOT NULL, the new constraint name does not matches the existing domain's not-null constraint
create domain d1 as int constraint nn not null; src4=# alter domain d1 add constraint nn1 not null; ERROR: cannot create not-null constraint "nn1" for domain "d1" DETAIL: A not-null constraint named "nn" already exists for domain "d1". However, repeated ALTER DOMAIN SET NOT NULL or ALTER DOMAIN ADD NOT NULL statements are allowed, This aligns with the NOT NULL constraints on tables. No need to worry about CREATE DOMAIN. We already disallow multiple NOT NULL constraints in CREATE DOMAIN. Like this would fail: create domain d2 as text collate "C" constraint nn not null constraint nn2 not null; -- jian https://www.enterprisedb.com/
From b409a5a32f9f2c5469c6886d7f8bc19610b08708 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Sun, 1 Mar 2026 09:45:06 +0800 Subject: [PATCH v1 1/1] Reject ADD CONSTRAINT NOT NULL if name mismatches existing domain constraint When using ALTER DOMAIN ... ADD CONSTRAINT to add a not-null constraint with an explicit name to a domain, we have to ensure that if the domain is already marked NOT NULL, the provided name matches the existing domain constraint name. Failing to do so could lead to confusion regarding which constraint object actually enforces the rule. This patch adds a check to throw an error if the user tries to add a named not-null constraint to a domain that already has one with a different name. discussion: https://postgr.es/m/ relate discussion: https://git.postgresql.org/cgit/postgresql.git/commit/?id=96e2af605043974137d84edf5c0a24561956919e commitfest entry: https://commitfest.postgresql.org/patch/ --- src/backend/commands/typecmds.c | 25 +++++++++++++++++++++++++ src/test/regress/expected/domain.out | 11 ++++++++++- src/test/regress/sql/domain.sql | 6 +++++- 3 files changed, 40 insertions(+), 2 deletions(-) diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 3dab6bb5a79..2c809ff9578 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -3026,6 +3026,31 @@ AlterDomainAddConstraint(List *names, Node *newConstraint, /* Is the domain already set NOT NULL? */ if (typTup->typnotnull) { + if (constr->conname) + { + Form_pg_constraint conform; + + HeapTuple conTup = findDomainNotNullConstraint(domainoid); + + if (conTup == NULL) + elog(ERROR, "could not find not-null constraint on domain \"%s\"", NameStr(typTup->typname)); + + conform = (Form_pg_constraint) GETSTRUCT(conTup); + + /* + * If a name was specified, for the new NOT NULL constraint, + * ensure that the existing NOT NULL constraint uses the same + * name; otherwise, throw an error. + */ + if (strcmp(constr->conname, NameStr(conform->conname)) != 0) + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot create not-null constraint \"%s\" for domain \"%s\"", + constr->conname, NameStr(typTup->typname)), + errdetail("A not-null constraint named \"%s\" already exists for domain \"%s\".", + NameStr(conform->conname), NameStr(typTup->typname))); + } + table_close(typrel, RowExclusiveLock); return address; } diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out index 62a48a523a2..c3cfd24275b 100644 --- a/src/test/regress/expected/domain.out +++ b/src/test/regress/expected/domain.out @@ -893,7 +893,16 @@ select count(*) from pg_constraint where contypid = 'connotnull'::regtype and co 1 (1 row) -alter domain connotnull add constraint constr1bis not null; -- redundant +alter domain connotnull add constraint constr1bis not null; -- error +ERROR: cannot create not-null constraint "constr1bis" for domain "connotnull" +DETAIL: A not-null constraint named "constr1" already exists for domain "connotnull". +alter domain connotnull add constraint constr1 not null; -- redundant +alter domain connotnull add not null; -- redundant +alter domain connotnull set not null; -- redundant +alter domain connotnull add not null not valid; -- error +ERROR: NOT NULL constraints cannot be marked NOT VALID +LINE 1: alter domain connotnull add not null not valid; + ^ select count(*) from pg_constraint where contypid = 'connotnull'::regtype and contype = 'n'; count ------- diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql index b8f5a639712..a594daf1f47 100644 --- a/src/test/regress/sql/domain.sql +++ b/src/test/regress/sql/domain.sql @@ -513,7 +513,11 @@ update domconnotnulltest set col2 = 6; alter domain connotnull add constraint constr1 not null; select count(*) from pg_constraint where contypid = 'connotnull'::regtype and contype = 'n'; -alter domain connotnull add constraint constr1bis not null; -- redundant +alter domain connotnull add constraint constr1bis not null; -- error +alter domain connotnull add constraint constr1 not null; -- redundant +alter domain connotnull add not null; -- redundant +alter domain connotnull set not null; -- redundant +alter domain connotnull add not null not valid; -- error select count(*) from pg_constraint where contypid = 'connotnull'::regtype and contype = 'n'; \dD connotnull -- 2.34.1
