On Thu, Sep 25, 2025 at 5:46 PM Álvaro Herrera <[email protected]> wrote:
> >
> > CREATE TABLE tx3 (x int not null not enforced);
> >
> > can be dumped as:
> >
> > CREATE TABLE public.tx3 (x integer);
> > ALTER TABLE public.tx3 ADD CONSTRAINT tx3_x_not_null NOT NULL x NOT
> > ENFORCED;
> > ---------------
> > note: currently not enforced check constraint is dumped separately.
>
> Hmm, I wonder what's the reason for this. Seems quite useless. Why
> wouldn't we dump unenforced constraint together with the table? The
> case is different from invalid constraints, which have to be created
> after data is loaded.
>
> --
> Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
hi.
If NOT ENFORCED NOT NULL is dumped as separate ALTER TABLE entry:
ALTER TABLE ... ADD CONSTRAINT ... NOT NULL NOT ENFORCED;
then the dump behavior is effectively the same as NOT NULL NOT VALID.
However, if we intend to emit NOT ENFORCED together with other column
attributes during dump, we must distinguish between NOT VALID ENFORCED and
NOT VALID NOT ENFORCED.
For example, that would require refactoring the code snippet below in
the `flagInhAttrs()` function.
/*
* Keep track of whether all the parents that have a
* not-null constraint on this column have it as NOT
* VALID; if they all are, arrange to have it printed for
* this column. If at least one parent has it as valid,
* there's no need.
*/
if (fout->remoteVersion >= 180000 &&
parent->notnull_constrs[inhAttrInd] &&
!parent->notnull_invalid[inhAttrInd])
allNotNullsInvalid = false;
But I feel like such refactoring is unappealing; refactoring it would also
require more comments to explain it, All of this would introduce additional
complexity.
Given the relatively limited benefit,
NOT NULL NOT ENFORCED constraints as a separate ALTER TABLE statements in
pg_dump output should be fine, IMHO.
--
jian
https://www.enterprisedb.com/
From a4ae5847cbc68bd427c075faeb7866ae9418ea87 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 23 Feb 2026 17:42:28 +0800
Subject: [PATCH v3 1/1] NOT NULL NOT ENFORCED
this will remove sql_features.txt item F492: "Optional table constraint
enforcement" remarks: "except not-null constraints". See [1].
main points about NOT NULL NOT ENFORCED
* one column can have at most one NOT-NULL constraint, regardless constraints
property (not enforced or enforced)
* If column already have not enforced not-null constraint then:
ALTER TABLE ALTER COLUMN SET NOT NULL: error out, cannot validate not
enforced not-null constraint
ALTER TABLE ADD NOT NULL: error out, cannot add another not-null constraint,
one column can only have one.
not null in partitioned table:
* If the partitioned table has an enforced not-null constraint, its partitions
cannot have not enforced.
* If the partitioned table has a NOT ENFORCED not-null constraint, its
partitions may have either ENFORCED or NOT ENFORCED not-null constraints, but
the constraint itself is still required.
not null in table inheritance:
OK: parent is not enforced, while child is enforced
NOT OK: parent is enforced, while child is not enforced
If a column inherits from multiple tables and the ancestor tables have
conflicting ENFORCED statuses, raise an error.
commitfest: https://commitfest.postgresql.org/patch/6029
reference: https://git.postgresql.org/cgit/postgresql.git/commit/?id=a379061a22a8fdf421e1a457cc6af8503def6252
discussion: https://postgr.es/m/CACJufxFbH1_9BDow=4nmsdbflsoakigd5hxo6bouwjzayhb...@mail.gmail.com
---
doc/src/sgml/catalogs.sgml | 2 +-
doc/src/sgml/ref/alter_table.sgml | 4 +-
doc/src/sgml/ref/create_table.sgml | 4 +-
src/backend/catalog/heap.c | 67 +++++--
src/backend/catalog/pg_constraint.c | 49 ++++--
src/backend/catalog/sql_features.txt | 2 +-
src/backend/commands/tablecmds.c | 125 ++++++++++---
src/backend/parser/gram.y | 2 +-
src/backend/parser/parse_utilcmd.c | 83 ++++++---
src/backend/utils/cache/relcache.c | 4 +-
src/bin/pg_dump/t/002_pg_dump.pl | 31 ++++
src/bin/psql/describe.c | 20 ++-
src/include/access/tupdesc.h | 2 +-
src/include/catalog/heap.h | 2 +-
src/include/catalog/pg_constraint.h | 3 +-
src/test/regress/expected/constraints.out | 166 ++++++++++++++++++
.../regress/expected/create_table_like.out | 22 +++
src/test/regress/expected/inherit.out | 130 ++++++++++++++
src/test/regress/sql/constraints.sql | 91 ++++++++++
src/test/regress/sql/create_table_like.sql | 12 ++
src/test/regress/sql/inherit.sql | 70 ++++++++
21 files changed, 795 insertions(+), 96 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index e7067c84ece..0cba925e68b 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1263,7 +1263,7 @@
<structfield>attnotnull</structfield> <type>bool</type>
</para>
<para>
- This column has a (possibly invalid) not-null constraint.
+ This column has an enforced (possibly invalid) not-null constraint.
</para></entry>
</row>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1bd479c917a..33fc399b8ac 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1685,8 +1685,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Adding a <literal>CHECK</literal> or <literal>NOT NULL</literal>
constraint requires scanning the table to verify that existing rows meet the
constraint, but does not require a table rewrite. If a <literal>CHECK</literal>
- constraint is added as <literal>NOT ENFORCED</literal>, no verification will
- be performed.
+ or <literal>NOT NULL</literal> constraint is added as <literal>NOT ENFORCED</literal>,
+ no verification will be performed.
</para>
<para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 77c5a763d45..ecdb6738f87 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1425,8 +1425,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- This is currently only supported for foreign key and <literal>CHECK</literal>
- constraints.
+ This is currently only supported for foreign key, <literal>CHECK</literal>
+ and <literal>NOT NULL</literal> constraints.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 5748aa9a1a9..9045612deb2 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -2253,7 +2253,7 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr,
static Oid
StoreRelNotNull(Relation rel, const char *nnname, AttrNumber attnum,
bool is_validated, bool is_local, int inhcount,
- bool is_no_inherit)
+ bool is_no_inherit, bool is_enforced)
{
Oid constrOid;
@@ -2265,7 +2265,7 @@ StoreRelNotNull(Relation rel, const char *nnname, AttrNumber attnum,
CONSTRAINT_NOTNULL,
false,
false,
- true, /* Is Enforced */
+ is_enforced, /* Is Enforced */
is_validated,
InvalidOid,
RelationGetRelid(rel),
@@ -2627,17 +2627,19 @@ AddRelationNewConstraints(Relation rel,
strVal(linitial(cdef->keys))));
Assert(cdef->initially_valid != cdef->skip_validation);
+ Assert(cdef->is_enforced || !cdef->initially_valid);
/*
* If the column already has a not-null constraint, we don't want
* to add another one; adjust inheritance status as needed. This
* also checks whether the existing constraint matches the
- * requested validity.
+ * requested validity, enforceability.
*/
if (AdjustNotNullInheritance(RelationGetRelid(rel), colnum,
cdef->conname,
is_local, cdef->is_no_inherit,
- cdef->skip_validation))
+ cdef->skip_validation,
+ cdef->is_enforced))
continue;
/*
@@ -2668,7 +2670,8 @@ AddRelationNewConstraints(Relation rel,
cdef->initially_valid,
is_local,
inhcount,
- cdef->is_no_inherit);
+ cdef->is_no_inherit,
+ cdef->is_enforced);
nncooked = palloc_object(CookedConstraint);
nncooked->contype = CONSTR_NOTNULL;
@@ -2676,7 +2679,7 @@ AddRelationNewConstraints(Relation rel,
nncooked->name = nnname;
nncooked->attnum = colnum;
nncooked->expr = NULL;
- nncooked->is_enforced = true;
+ nncooked->is_enforced = cdef->is_enforced;
nncooked->skip_validation = cdef->skip_validation;
nncooked->is_local = is_local;
nncooked->inhcount = inhcount;
@@ -2952,7 +2955,7 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
/*
* A column can only have one not-null constraint, so discard any
* additional ones that appear for columns we already saw; but check
- * that the NO INHERIT flags match.
+ * that the NO INHERIT, [NOT] ENFORCED flags match.
*/
for (int restpos = outerpos + 1; restpos < list_length(constraints);)
{
@@ -2968,6 +2971,12 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
errmsg("conflicting NO INHERIT declaration for not-null constraint on column \"%s\"",
strVal(linitial(constr->keys))));
+ if (other->is_enforced != constr->is_enforced)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting NOT ENFORCED declaration for not-null constraint on column \"%s\"",
+ strVal(linitial(constr->keys))));
+
/*
* Preserve constraint name if one is specified, but raise an
* error if conflicting ones are specified.
@@ -3013,6 +3022,17 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
strVal(linitial(constr->keys))),
errdetail("The column has an inherited not-null constraint.")));
+ /*
+ * If we get a ENFORCED constraint from the parent, having a
+ * local NOT ENFORCED one doesn't work.
+ */
+ if (old->is_enforced && !constr->is_enforced)
+ ereport(ERROR,
+ errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot define not-null constraint with NOT ENFORCED on column \"%s\"",
+ strVal(linitial(constr->keys))),
+ errdetail("The column has an inherited ENFORCED not-null constraint."));
+
inhcount++;
old_notnulls = foreach_delete_current(old_notnulls, old);
}
@@ -3047,11 +3067,15 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
nnnames);
nnnames = lappend(nnnames, conname);
- StoreRelNotNull(rel, conname,
- attnum, true, true,
- inhcount, constr->is_no_inherit);
+ Assert(constr->is_enforced || constr->skip_validation);
- nncols = lappend_int(nncols, attnum);
+ StoreRelNotNull(rel, conname, attnum,
+ !constr->skip_validation,
+ true, inhcount, constr->is_no_inherit,
+ constr->is_enforced);
+
+ if (constr->is_enforced)
+ nncols = lappend_int(nncols, attnum);
}
/*
@@ -3096,6 +3120,18 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
conname = other->name;
inhcount++;
+
+ /*
+ * A column can inherit multiple not-null constraints. If one
+ * is not enforced, another one is enforced then we will
+ * install an enforced one.
+ */
+ if (other->is_enforced != cooked->is_enforced)
+ {
+ cooked->is_enforced = true;
+ cooked->skip_validation = false;
+ }
+
old_notnulls = list_delete_nth_cell(old_notnulls, restpos);
}
else
@@ -3126,10 +3162,13 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
nnnames = lappend(nnnames, conname);
/* ignore the origin constraint's is_local and inhcount */
- StoreRelNotNull(rel, conname, cooked->attnum, true,
- false, inhcount, false);
+ StoreRelNotNull(rel, conname, cooked->attnum,
+ cooked->is_enforced ? true : false,
+ false, inhcount, false,
+ cooked->is_enforced);
- nncols = lappend_int(nncols, cooked->attnum);
+ if (cooked->is_enforced)
+ nncols = lappend_int(nncols, cooked->attnum);
}
return nncols;
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index b12765ae691..20d811af79e 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -100,9 +100,10 @@ CreateConstraintEntry(const char *constraintName,
ObjectAddresses *addrs_auto;
ObjectAddresses *addrs_normal;
- /* Only CHECK or FOREIGN KEY constraint can be not enforced */
- Assert(isEnforced || constraintType == CONSTRAINT_CHECK ||
- constraintType == CONSTRAINT_FOREIGN);
+ /* Only CHECK, FOREIGN KEY, NOT NULL constraint can be not enforced */
+ Assert(isEnforced || (constraintType == CONSTRAINT_CHECK ||
+ constraintType == CONSTRAINT_FOREIGN ||
+ constraintType == CONSTRAINT_NOTNULL));
/* NOT ENFORCED constraint must be NOT VALID */
Assert(isEnforced || !isValidated);
@@ -580,8 +581,8 @@ ChooseConstraintName(const char *name1, const char *name2,
}
/*
- * Find and return a copy of the pg_constraint tuple that implements a
- * (possibly not valid) not-null constraint for the given column of the
+ * Find and return a copy of the pg_constraint tuple that implements a (possibly
+ * not valid or not enforced) not-null constraint for the given column of the
* given relation. If no such constraint exists, return NULL.
*
* XXX This would be easier if we had pg_attribute.notnullconstr with the OID
@@ -634,8 +635,8 @@ findNotNullConstraintAttnum(Oid relid, AttrNumber attnum)
/*
* Find and return a copy of the pg_constraint tuple that implements a
- * (possibly not valid) not-null constraint for the given column of the
- * given relation.
+ * (possibly not valid or not enforced) not-null constraint for the given column
+ * of the given relation.
* If no such column or no such constraint exists, return NULL.
*/
HeapTuple
@@ -728,8 +729,8 @@ extractNotNullColumn(HeapTuple constrTup)
* If no not-null constraint is found for the column, return false.
* Caller can create one.
*
- * If a constraint exists but the connoinherit flag is not what the caller
- * wants, throw an error about the incompatibility. If the desired
+ * If a constraint exists but the connoinherit, conenforced flag is not what the
+ * caller wants, throw an error about the incompatibility. If the desired
* constraint is valid but the existing constraint is not valid, also
* throw an error about that (the opposite case is acceptable). If
* the proposed constraint has a different name, also throw an error.
@@ -740,7 +741,8 @@ extractNotNullColumn(HeapTuple constrTup)
*/
bool
AdjustNotNullInheritance(Oid relid, AttrNumber attnum, const char *new_conname,
- bool is_local, bool is_no_inherit, bool is_notvalid)
+ bool is_local, bool is_no_inherit, bool is_notvalid,
+ bool is_enforced)
{
HeapTuple tup;
@@ -770,7 +772,7 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum, const char *new_conname,
* Throw an error if the existing constraint is NOT VALID and caller
* wants a valid one.
*/
- if (!is_notvalid && !conform->convalidated)
+ if (!is_notvalid && !conform->convalidated && conform->conenforced)
ereport(ERROR,
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("incompatible NOT VALID constraint \"%s\" on relation \"%s\"",
@@ -794,6 +796,26 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum, const char *new_conname,
errdetail("A not-null constraint named \"%s\" already exists for this column.",
NameStr(conform->conname)));
+ /*
+ * If the ENFORCED status we're asked for doesn't match what the
+ * existing constraint has, then throw an error.
+ */
+ if (is_enforced != conform->conenforced)
+ {
+ if (is_enforced)
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot change not enforced NOT NULL constraint \"%s\" on relation \"%s\" to enforced",
+ NameStr(conform->conname), get_rel_name(relid)),
+ errhint("You might need to ensure the existing constraint is enforced."));
+ else
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot change enforced NOT NULL constraint \"%s\" on relation \"%s\" to not enforced",
+ NameStr(conform->conname), get_rel_name(relid)),
+ errhint("You might need to ensure the existing constraint is not enforced."));
+ }
+
if (!is_local)
{
if (pg_add_s16_overflow(conform->coninhcount, 1,
@@ -824,6 +846,7 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum, const char *new_conname,
* RelationGetNotNullConstraints
* Return the list of not-null constraints for the given rel
*
+ * The returned not-null constraints possibly not enforced!
* Caller can request cooked constraints, or raw.
*
* This is seldom needed, so we just scan pg_constraint each time.
@@ -870,7 +893,7 @@ RelationGetNotNullConstraints(Oid relid, bool cooked, bool include_noinh)
cooked->name = pstrdup(NameStr(conForm->conname));
cooked->attnum = colnum;
cooked->expr = NULL;
- cooked->is_enforced = true;
+ cooked->is_enforced = conForm->conenforced;
cooked->skip_validation = !conForm->convalidated;
cooked->is_local = true;
cooked->inhcount = 0;
@@ -890,7 +913,7 @@ RelationGetNotNullConstraints(Oid relid, bool cooked, bool include_noinh)
constr->location = -1;
constr->keys = list_make1(makeString(get_attname(relid, colnum,
false)));
- constr->is_enforced = true;
+ constr->is_enforced = conForm->conenforced;
constr->skip_validation = !conForm->convalidated;
constr->initially_valid = conForm->convalidated;
constr->is_no_inherit = conForm->connoinherit;
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 3a8ad201607..73620edf04e 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -281,7 +281,7 @@ F461 Named character sets NO
F471 Scalar subquery values YES
F481 Expanded NULL predicate YES
F491 Constraint management YES
-F492 Optional table constraint enforcement YES except not-null constraints
+F492 Optional table constraint enforcement YES
F501 Features and conformance views YES
F501 Features and conformance views 01 SQL_FEATURES view YES
F501 Features and conformance views 02 SQL_SIZING view YES
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index df1ba112b35..fe96189cea4 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -506,6 +506,7 @@ static void set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
static ObjectAddress ATExecSetNotNull(List **wqueue, Relation rel,
char *conName, char *colName,
bool recurse, bool recursing,
+ bool is_enforced,
LOCKMODE lockmode);
static bool NotNullImpliedByRelConstraints(Relation rel, Form_pg_attribute attr);
static bool ConstraintImpliedByRelConstraint(Relation scanrel,
@@ -2759,13 +2760,16 @@ MergeAttributes(List *columns, const List *supers, char relpersistence,
inherited_defaults = cols_with_defaults = NIL;
/*
- * Request attnotnull on columns that have a not-null constraint
- * that's not marked NO INHERIT (even if not valid).
+ * Request attnotnull on columns that have an enforced not-null
+ * constraint that's not marked NO INHERIT (even if not valid).
*/
nnconstrs = RelationGetNotNullConstraints(RelationGetRelid(relation),
true, false);
foreach_ptr(CookedConstraint, cc, nnconstrs)
- nncols = bms_add_member(nncols, cc->attnum);
+ {
+ if (cc->is_enforced)
+ nncols = bms_add_member(nncols, cc->attnum);
+ }
for (AttrNumber parent_attno = 1; parent_attno <= tupleDesc->natts;
parent_attno++)
@@ -5433,7 +5437,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
break;
case AT_SetNotNull: /* ALTER COLUMN SET NOT NULL */
address = ATExecSetNotNull(wqueue, rel, NULL, cmd->name,
- cmd->recurse, false, lockmode);
+ cmd->recurse, false, true, lockmode);
break;
case AT_SetExpression:
address = ATExecSetExpression(tab, rel, cmd->name, cmd->def, lockmode);
@@ -7776,6 +7780,7 @@ add_column_collation_dependency(Oid relid, int32 attnum, Oid collid)
*
* Return the address of the modified column. If the column was already
* nullable, InvalidObjectAddress is returned.
+ * This will drop the not enforced not-null constraint too.
*/
static ObjectAddress
ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
@@ -7804,13 +7809,6 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
ObjectAddressSubSet(address, RelationRelationId,
RelationGetRelid(rel), attnum);
- /* If the column is already nullable there's nothing to do. */
- if (!attTup->attnotnull)
- {
- table_close(attr_rel, RowExclusiveLock);
- return InvalidObjectAddress;
- }
-
/* Prevent them from altering a system attribute */
if (attnum <= 0)
ereport(ERROR,
@@ -7849,8 +7847,17 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
*/
conTup = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum);
if (conTup == NULL)
- elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation \"%s\"",
- colName, RelationGetRelationName(rel));
+ {
+ if (attTup->attnotnull)
+ elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation \"%s\"",
+ colName, RelationGetRelationName(rel));
+ else
+ {
+ /* Skip if no NOT NULL constraint exists (including NOT ENFORCED). */
+ table_close(attr_rel, RowExclusiveLock);
+ return InvalidObjectAddress;
+ }
+ }
/* The normal case: we have a pg_constraint row, remove it */
dropconstraint_internal(rel, conTup, DROP_RESTRICT, recurse, false,
@@ -7947,10 +7954,13 @@ set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
*
* We must recurse to child tables during execution, rather than using
* ALTER TABLE's normal prep-time recursion.
+ * When the is_enforced flag is false, the newly added NOT NULL constraint will
+ * be not enforced. This supports potential future syntax such as
+ * ALTER TABLE ALTER COLUMN SET NOT NULL NOT ENFORCED
*/
static ObjectAddress
ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
- bool recurse, bool recursing, LOCKMODE lockmode)
+ bool recurse, bool recursing, bool is_enforced, LOCKMODE lockmode)
{
HeapTuple tuple;
AttrNumber attnum;
@@ -7985,7 +7995,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
errmsg("cannot alter system column \"%s\"",
colName)));
- /* See if there's already a constraint */
+ /* See if there's already a constraint. It maybe not enforced! */
tuple = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum);
if (HeapTupleIsValid(tuple))
{
@@ -8002,6 +8012,13 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
NameStr(conForm->conname),
RelationGetRelationName(rel)));
+ if (is_enforced && !conForm->conenforced)
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot validate NOT ENFORCED constraint \"%s\" on relation \"%s\"",
+ NameStr(conForm->conname),
+ RelationGetRelationName(rel)));
+
/*
* If we find an appropriate constraint, we're almost done, but just
* need to change some properties on it: if we're recursing, increment
@@ -8021,7 +8038,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
conForm->conislocal = true;
changed = true;
}
- else if (!conForm->convalidated)
+ else if (is_enforced && !conForm->convalidated && conForm->conenforced)
{
/*
* Flip attnotnull and convalidated, and also validate the
@@ -8082,6 +8099,9 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
constraint = makeNotNullConstraint(makeString(colName));
constraint->is_no_inherit = is_no_inherit;
constraint->conname = conName;
+ constraint->is_enforced = is_enforced;
+ constraint->initially_valid = is_enforced;
+ constraint->skip_validation = !is_enforced;
/* and do it */
cooked = AddRelationNewConstraints(rel, NIL, list_make1(constraint),
@@ -8093,7 +8113,8 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
RelationGetRelid(rel), attnum);
/* Mark pg_attribute.attnotnull for the column and queue validation */
- set_attnotnull(wqueue, rel, attnum, true, true);
+ if (ccon->is_enforced)
+ set_attnotnull(wqueue, rel, attnum, true, true);
/*
* Recurse to propagate the constraint to children that don't have one.
@@ -8112,7 +8133,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
CommandCounterIncrement();
ATExecSetNotNull(wqueue, childrel, conName, colName,
- recurse, true, lockmode);
+ recurse, true, is_enforced, lockmode);
table_close(childrel, NoLock);
}
}
@@ -9634,7 +9655,7 @@ verifyNotNullPKCompatible(HeapTuple tuple, const char *colname)
"ALTER TABLE ... ALTER CONSTRAINT ... INHERIT"));
/* an unvalidated constraint is no good */
- if (!conForm->convalidated)
+ if (!conForm->convalidated && conForm->conenforced)
ereport(ERROR,
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("cannot create primary key on column \"%s\"", colname),
@@ -9644,6 +9665,17 @@ verifyNotNullPKCompatible(HeapTuple tuple, const char *colname)
get_rel_name(conForm->conrelid), "NOT VALID"),
errhint("You might need to validate it using %s.",
"ALTER TABLE ... VALIDATE CONSTRAINT"));
+
+ /* a not enforced constraint is no good */
+ if (!conForm->conenforced)
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot create primary key on column \"%s\"", colname),
+ /*- translator: fourth %s is a constraint characteristic such as NOT ENFORCED */
+ errdetail("The constraint \"%s\" on column \"%s\" of table \"%s\", marked %s, is incompatible with a primary key.",
+ NameStr(conForm->conname), colname,
+ get_rel_name(conForm->conrelid), "NOT ENFORCED"),
+ errhint("You might need to ensure the existing constraint is enforced."));
}
/*
@@ -10013,9 +10045,9 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
* If adding a valid not-null constraint, set the pg_attribute flag
* and tell phase 3 to verify existing rows, if needed. For an
* invalid constraint, just set attnotnull, without queueing
- * verification.
+ * verification. No need to set attnotnull for not enforced not-null.
*/
- if (constr->contype == CONSTR_NOTNULL)
+ if (constr->contype == CONSTR_NOTNULL && ccon->is_enforced)
set_attnotnull(wqueue, rel, ccon->attnum,
!constr->skip_validation,
!constr->skip_validation);
@@ -12713,7 +12745,9 @@ ATExecAlterConstrInheritability(List **wqueue, ATAlterConstraint *cmdcon,
Relation childrel = table_open(childoid, NoLock);
addr = ATExecSetNotNull(wqueue, childrel, NameStr(currcon->conname),
- colName, true, true, lockmode);
+ colName, true, true,
+ currcon->conenforced,
+ lockmode);
if (OidIsValid(addr.objectId))
CommandCounterIncrement();
table_close(childrel, NoLock);
@@ -17581,9 +17615,31 @@ MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel, bool ispart
if (parent_att->attnotnull && !child_att->attnotnull)
{
HeapTuple contup;
+ HeapTuple childcontup;
+ childcontup = findNotNullConstraintAttnum(RelationGetRelid(child_rel),
+ child_att->attnum);
contup = findNotNullConstraintAttnum(RelationGetRelid(parent_rel),
parent_att->attnum);
+
+ if (HeapTupleIsValid(childcontup) && HeapTupleIsValid(contup))
+ {
+ Form_pg_constraint child_con = (Form_pg_constraint) GETSTRUCT(childcontup);
+ Form_pg_constraint parent_con = (Form_pg_constraint) GETSTRUCT(contup);
+
+ Assert(parent_con->conenforced);
+ Assert(!child_con->conenforced);
+
+ /*
+ * An unenforced NOT NULL constraint on the child cannot
+ * be merged with an enforced constraint on the parent.
+ */
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("constraint \"%s\" conflicts with NOT ENFORCED constraint on child table \"%s\"",
+ NameStr(child_con->conname), RelationGetRelationName(child_rel)));
+ }
+
if (HeapTupleIsValid(contup) &&
!((Form_pg_constraint) GETSTRUCT(contup))->connoinherit)
ereport(ERROR,
@@ -17835,13 +17891,24 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel)
if (!found)
{
if (parent_con->contype == CONSTRAINT_NOTNULL)
- ereport(ERROR,
- errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("column \"%s\" in child table \"%s\" must be marked NOT NULL",
- get_attname(parent_relid,
- extractNotNullColumn(parent_tuple),
- false),
- RelationGetRelationName(child_rel)));
+ {
+ if (parent_con->conenforced)
+ ereport(ERROR,
+ errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("column \"%s\" in child table \"%s\" must be marked NOT NULL",
+ get_attname(parent_relid,
+ extractNotNullColumn(parent_tuple),
+ false),
+ RelationGetRelationName(child_rel)));
+ else
+ ereport(ERROR,
+ errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("column \"%s\" in child table \"%s\" must be marked NOT NULL NOT ENFORCED",
+ get_attname(parent_relid,
+ extractNotNullColumn(parent_tuple),
+ false),
+ RelationGetRelationName(child_rel)));
+ }
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c567252acc4..cda99ca22c2 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4339,7 +4339,7 @@ ConstraintElem:
n->location = @1;
n->keys = list_make1(makeString($3));
processCASbits($4, @4, "NOT NULL",
- NULL, NULL, NULL, &n->skip_validation,
+ NULL, NULL, &n->is_enforced, &n->skip_validation,
&n->is_no_inherit, yyscanner);
n->initially_valid = !n->skip_validation;
$$ = (Node *) n;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index cc244c49e9e..dfbee89e558 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -320,6 +320,10 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
{
char *colname = strVal(linitial(nn->keys));
+ /* Do not set is_not_null to true for not enforced not-null constraint */
+ if (!nn->is_enforced)
+ continue;
+
foreach_node(ColumnDef, cd, cxt.columns)
{
/* not our column? */
@@ -716,6 +720,10 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
* disallow it here as well. Maybe AddRelationNotNullConstraints can be
* improved someday, so that it doesn't complain, and then we can remove
* the restriction for SERIAL and IDENTITY here as well.
+ *
+ * Note: The above explanation apply to NOT ENFORCED not-null constraint.
+ * disallow_noinherit_notnull treats NOT ENFORCED the same way as NO
+ * INHERIT.
*/
if (!disallow_noinherit_notnull)
{
@@ -776,6 +784,12 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
errmsg("conflicting NO INHERIT declarations for not-null constraints on column \"%s\"",
column->colname));
+ if (disallow_noinherit_notnull && !constraint->is_enforced)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting NOT ENFORCED declarations for not-null constraints on column \"%s\"",
+ column->colname));
+
/*
* If this is the first time we see this column being marked
* not-null, add the constraint entry and keep track of it.
@@ -795,6 +809,15 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
constraint->keys = list_make1(makeString(column->colname));
notnull_constraint = constraint;
+
+ /*
+ * NOT ENFORCED not-null constraint does not indicate data
+ * are all not-null, therefore can not set column
+ * pg_attribute.attnotnull to true.
+ */
+ if (!constraint->is_enforced)
+ column->is_not_null = false;
+
cxt->nnconstraints = lappend(cxt->nnconstraints, constraint);
}
else if (notnull_constraint)
@@ -1134,6 +1157,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
AclResult aclresult;
char *comment;
ParseCallbackState pcbstate;
+ List *lst = NIL;
setup_parser_errposition_callback(&pcbstate, cxt->pstate,
table_like_clause->relation->location);
@@ -1275,33 +1299,42 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
* Reproduce not-null constraints, if any, by copying them. We do this
* regardless of options given.
*/
- if (tupleDesc->constr && tupleDesc->constr->has_not_null)
- {
- List *lst;
+ lst = RelationGetNotNullConstraints(RelationGetRelid(relation), false,
+ true);
+ cxt->nnconstraints = list_concat(cxt->nnconstraints, lst);
- lst = RelationGetNotNullConstraints(RelationGetRelid(relation), false,
- true);
- cxt->nnconstraints = list_concat(cxt->nnconstraints, lst);
+ /*
+ * When creating a new relation, marking the enforced not-null constraint
+ * as not valid doesn't make sense, so we treat it as valid
+ * unconditionally.
+ */
+ foreach_node(Constraint, nnconstr, lst)
+ {
+ if (nnconstr->is_enforced)
+ {
+ nnconstr->skip_validation = false;
+ nnconstr->initially_valid = true;
+ }
+ }
- /* Copy comments on not-null constraints */
- if (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS)
+ /* Copy comments on not-null constraints */
+ if (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS)
+ {
+ foreach_node(Constraint, nnconstr, lst)
{
- foreach_node(Constraint, nnconstr, lst)
+ if ((comment = GetComment(get_relation_constraint_oid(RelationGetRelid(relation),
+ nnconstr->conname, false),
+ ConstraintRelationId,
+ 0)) != NULL)
{
- if ((comment = GetComment(get_relation_constraint_oid(RelationGetRelid(relation),
- nnconstr->conname, false),
- ConstraintRelationId,
- 0)) != NULL)
- {
- CommentStmt *stmt = makeNode(CommentStmt);
+ CommentStmt *stmt = makeNode(CommentStmt);
- stmt->objtype = OBJECT_TABCONSTRAINT;
- stmt->object = (Node *) list_make3(makeString(cxt->relation->schemaname),
- makeString(cxt->relation->relname),
- makeString(nnconstr->conname));
- stmt->comment = comment;
- cxt->alist = lappend(cxt->alist, stmt);
- }
+ stmt->objtype = OBJECT_TABCONSTRAINT;
+ stmt->object = (Node *) list_make3(makeString(cxt->relation->schemaname),
+ makeString(cxt->relation->relname),
+ makeString(nnconstr->conname));
+ stmt->comment = comment;
+ cxt->alist = lappend(cxt->alist, stmt);
}
}
}
@@ -4310,7 +4343,8 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
case CONSTR_ATTR_ENFORCED:
if (lastprimarycon == NULL ||
(lastprimarycon->contype != CONSTR_CHECK &&
- lastprimarycon->contype != CONSTR_FOREIGN))
+ lastprimarycon->contype != CONSTR_FOREIGN &&
+ lastprimarycon->contype != CONSTR_NOTNULL))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("misplaced ENFORCED clause"),
@@ -4327,7 +4361,8 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
case CONSTR_ATTR_NOT_ENFORCED:
if (lastprimarycon == NULL ||
(lastprimarycon->contype != CONSTR_CHECK &&
- lastprimarycon->contype != CONSTR_FOREIGN))
+ lastprimarycon->contype != CONSTR_FOREIGN &&
+ lastprimarycon->contype != CONSTR_NOTNULL))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("misplaced NOT ENFORCED clause"),
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 6b634c9fff1..e78441761bb 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4618,7 +4618,7 @@ CheckNNConstraintFetch(Relation relation)
bool isnull;
/*
- * If this is a not-null constraint, then only look at it if it's
+ * If this is an enforced not-null constraint, then only look at it if it's
* invalid, and if so, mark the TupleDesc entry as known invalid.
* Otherwise move on. We'll mark any remaining columns that are still
* in UNKNOWN state as known valid later. This allows us not to have
@@ -4627,7 +4627,7 @@ CheckNNConstraintFetch(Relation relation)
*/
if (conform->contype == CONSTRAINT_NOTNULL)
{
- if (!conform->convalidated)
+ if (!conform->convalidated && conform->conenforced)
{
AttrNumber attnum;
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index a8dcc2b5c75..8a21a5a5369 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -968,6 +968,37 @@ my %tests = (
},
},
+ 'CONSTRAINT NOT NULL NOT ENFORCED' => {
+ create_sql => 'CREATE TABLE dump_test.test_table_nn0 (
+ col1 int, CONSTRAINT nn NOT NULL col1 NOT ENFORCED);
+ COMMENT ON CONSTRAINT nn ON dump_test.test_table_nn0 IS \'nn comment is not enfoced\';',
+ regexp => qr/^
+ \QALTER TABLE dump_test.test_table_nn0\E \n^\s+
+ \QADD CONSTRAINT nn NOT NULL col1 NOT ENFORCED;\E
+ /xm,
+ like => {
+ %full_runs, %dump_test_schema_runs, section_post_data => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_measurement => 1,
+ },
+ },
+
+ # This constraint is invalid (not enforced) therefore it goes in SECTION_POST_DATA
+ 'COMMENT ON CONSTRAINT ON test_table_nn0' => {
+ regexp => qr/^
+ \QCOMMENT ON CONSTRAINT nn ON dump_test.test_table_nn0 IS\E
+ /xm,
+ like => {
+ %full_runs, %dump_test_schema_runs, section_post_data => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_measurement => 1,
+ },
+ },
+
'CONSTRAINT NOT NULL / NOT VALID' => {
create_sql => 'CREATE TABLE dump_test.test_table_nn (
col1 int);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 571a6a003d5..4290563e82d 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3142,7 +3142,14 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&buf,
"SELECT c.conname, a.attname, c.connoinherit,\n"
" c.conislocal, c.coninhcount <> 0,\n"
- " c.convalidated\n"
+ " c.convalidated,\n");
+
+ if (pset.sversion >= 190000)
+ appendPQExpBufferStr(&buf, "c.conenforced\n");
+ else
+ appendPQExpBufferStr(&buf, "true as conenforced\n");
+
+ appendPQExpBuffer(&buf,
"FROM pg_catalog.pg_constraint c JOIN\n"
" pg_catalog.pg_attribute a ON\n"
" (a.attrelid = c.conrelid AND a.attnum = c.conkey[1])\n"
@@ -3166,15 +3173,20 @@ describeOneTableDetails(const char *schemaname,
bool islocal = PQgetvalue(result, i, 3)[0] == 't';
bool inherited = PQgetvalue(result, i, 4)[0] == 't';
bool validated = PQgetvalue(result, i, 5)[0] == 't';
+ bool enforced = PQgetvalue(result, i, 6)[0] == 't';
- printfPQExpBuffer(&buf, " \"%s\" NOT NULL \"%s\"%s%s",
+ printfPQExpBuffer(&buf, " \"%s\" NOT NULL \"%s\"%s",
PQgetvalue(result, i, 0),
PQgetvalue(result, i, 1),
PQgetvalue(result, i, 2)[0] == 't' ?
" NO INHERIT" :
islocal && inherited ? _(" (local, inherited)") :
- inherited ? _(" (inherited)") : "",
- !validated ? " NOT VALID" : "");
+ inherited ? _(" (inherited)") : "");
+
+ if (!enforced)
+ appendPQExpBufferStr(&buf, " NOT ENFORCED");
+ else if (!validated)
+ appendPQExpBufferStr(&buf, " NOT VALID");
printTableAddFooter(&cont, buf.data);
}
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h
index d46cdbf7a3c..abc877e9856 100644
--- a/src/include/access/tupdesc.h
+++ b/src/include/access/tupdesc.h
@@ -42,7 +42,7 @@ typedef struct TupleConstr
struct AttrMissing *missing; /* missing attributes values, NULL if none */
uint16 num_defval;
uint16 num_check;
- bool has_not_null; /* any not-null, including not valid ones */
+ bool has_not_null; /* any enforced not-null, including not valid ones */
bool has_generated_stored;
bool has_generated_virtual;
} TupleConstr;
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h
index 6c9ac812aa0..344d4bc5390 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -40,7 +40,7 @@ typedef struct CookedConstraint
char *name; /* name, or NULL if none */
AttrNumber attnum; /* which attr (only for NOTNULL, DEFAULT) */
Node *expr; /* transformed default or check expr */
- bool is_enforced; /* is enforced? (only for CHECK) */
+ bool is_enforced; /* is enforced? (for NOT NULL and CHECK) */
bool skip_validation; /* skip validation? (only for CHECK) */
bool is_local; /* constraint has local (non-inherited) def */
int16 inhcount; /* number of times constraint is inherited */
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index d5661b5bdff..b67dc3d539a 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -264,7 +264,8 @@ extern HeapTuple findNotNullConstraint(Oid relid, const char *colname);
extern HeapTuple findDomainNotNullConstraint(Oid typid);
extern AttrNumber extractNotNullColumn(HeapTuple constrTup);
extern bool AdjustNotNullInheritance(Oid relid, AttrNumber attnum, const char *new_conname,
- bool is_local, bool is_no_inherit, bool is_notvalid);
+ bool is_local, bool is_no_inherit, bool is_notvalid,
+ bool is_enforced);
extern List *RelationGetNotNullConstraints(Oid relid, bool cooked,
bool include_noinh);
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index a6fa9cacb72..041f6fa94cc 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -969,33 +969,139 @@ drop table notnull_tbl2, notnull_tbl3, notnull_tbl4, notnull_tbl5, notnull_tbl6;
-- error cases:
create table notnull_tbl_fail (a serial constraint foo not null constraint bar not null);
ERROR: conflicting not-null constraint names "foo" and "bar"
+create table notnull_tbl_fail (a serial constraint foo not null constraint bar not null not enforced);
+ERROR: conflicting NOT ENFORCED declarations for not-null constraints on column "a"
create table notnull_tbl_fail (a serial constraint foo not null no inherit constraint foo not null);
ERROR: conflicting NO INHERIT declarations for not-null constraints on column "a"
+create table notnull_tbl_fail (a serial constraint foo not null not enforced);
+ERROR: conflicting NOT ENFORCED declarations for not-null constraints on column "a"
create table notnull_tbl_fail (a int constraint foo not null, constraint foo not null a no inherit);
ERROR: conflicting NO INHERIT declaration for not-null constraint on column "a"
+create table notnull_tbl_fail (a int constraint foo not null, constraint foo not null a not enforced);
+ERROR: conflicting NOT ENFORCED declaration for not-null constraint on column "a"
create table notnull_tbl_fail (a serial constraint foo not null, constraint bar not null a);
ERROR: conflicting not-null constraint names "foo" and "bar"
+create table notnull_tbl_fail (a serial constraint foo not null, constraint bar not null a not enforced);
+ERROR: conflicting NOT ENFORCED declaration for not-null constraint on column "a"
create table notnull_tbl_fail (a serial, constraint foo not null a, constraint bar not null a);
ERROR: conflicting not-null constraint names "foo" and "bar"
+create table notnull_tbl_fail (a serial, constraint foo not null a, constraint bar not null a not enforced);
+ERROR: conflicting NOT ENFORCED declaration for not-null constraint on column "a"
create table notnull_tbl_fail (a serial, constraint foo not null a no inherit);
ERROR: conflicting NO INHERIT declaration for not-null constraint on column "a"
+create table notnull_tbl_fail (a serial, constraint foo not null a not enforced);
+ERROR: conflicting NOT ENFORCED declaration for not-null constraint on column "a"
create table notnull_tbl_fail (a serial not null no inherit);
ERROR: conflicting NO INHERIT declarations for not-null constraints on column "a"
+create table notnull_tbl_fail (a serial not null not enforced);
+ERROR: conflicting NOT ENFORCED declarations for not-null constraints on column "a"
create table notnull_tbl_fail (like notnull_tbl1, constraint foo2 not null a);
ERROR: conflicting not-null constraint names "foo" and "foo2"
+create table notnull_tbl_fail (like notnull_tbl1, constraint foo2 not null a not enforced);
+ERROR: conflicting NOT ENFORCED declaration for not-null constraint on column "a"
create table notnull_tbl_fail (a int primary key constraint foo not null no inherit);
ERROR: conflicting NO INHERIT declarations for not-null constraints on column "a"
+create table notnull_tbl_fail (a int primary key constraint foo not null not enforced);
+ERROR: conflicting NOT ENFORCED declarations for not-null constraints on column "a"
create table notnull_tbl_fail (a int not null no inherit primary key);
ERROR: conflicting NO INHERIT declarations for not-null constraints on column "a"
create table notnull_tbl_fail (a int primary key, not null a no inherit);
ERROR: conflicting NO INHERIT declaration for not-null constraint on column "a"
+create table notnull_tbl_fail (a int primary key, not null a not enforced);
+ERROR: conflicting NOT ENFORCED declaration for not-null constraint on column "a"
create table notnull_tbl_fail (a int, primary key(a), not null a no inherit);
ERROR: conflicting NO INHERIT declaration for not-null constraint on column "a"
+create table notnull_tbl_fail (a int, primary key(a), not null a not enforced);
+ERROR: conflicting NOT ENFORCED declaration for not-null constraint on column "a"
create table notnull_tbl_fail (a int generated by default as identity, constraint foo not null a no inherit);
ERROR: conflicting NO INHERIT declaration for not-null constraint on column "a"
+create table notnull_tbl_fail (a int generated by default as identity, constraint foo not null a not enforced);
+ERROR: conflicting NOT ENFORCED declaration for not-null constraint on column "a"
create table notnull_tbl_fail (a int generated by default as identity not null no inherit);
ERROR: conflicting NO INHERIT declarations for not-null constraints on column "a"
+create table notnull_tbl_fail (a int generated by default as identity not null not enforced);
+ERROR: conflicting NOT ENFORCED declarations for not-null constraints on column "a"
+alter table notnull_tbl1 add column b int not null not enforced; --ok
+alter table notnull_tbl1 alter column b add generated always as identity;
+ERROR: column "b" of relation "notnull_tbl1" must be declared NOT NULL before identity can be added
+alter table notnull_tbl1 add column c int not null not enforced, alter column c add generated always as identity;
+ERROR: column "c" of relation "notnull_tbl1" must be declared NOT NULL before identity can be added
+alter table notnull_tbl1 add column c int generated always as identity not null not enforced;
+ERROR: conflicting NOT ENFORCED declarations for not-null constraints on column "c"
+alter table notnull_tbl1 add column c serial not null not enforced;
+ERROR: conflicting NOT ENFORCED declarations for not-null constraints on column "c"
+alter table notnull_tbl1 add column c serial, add constraint notnull_tbl1_c_not_null not null c not enforced;
+ERROR: cannot change enforced NOT NULL constraint "notnull_tbl1_c_not_null" on relation "notnull_tbl1" to not enforced
+HINT: You might need to ensure the existing constraint is not enforced.
drop table notnull_tbl1;
+-- NOT NULL [NOT] ENFORCED
+CREATE TABLE ne_nn_tbl (x int, CONSTRAINT nn NOT NULL x NOT ENFORCED, NOT NULL x ENFORCED); --error
+ERROR: conflicting NOT ENFORCED declaration for not-null constraint on column "x"
+CREATE TABLE ne_nn_tbl (x int, CONSTRAINT nn NOT NULL x NOT ENFORCED);
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'nn';
+ pg_get_constraintdef
+-------------------------
+ NOT NULL x NOT ENFORCED
+(1 row)
+
+INSERT INTO ne_nn_tbl VALUES (NULL); --ok
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn ENFORCED; --error
+ERROR: cannot alter enforceability of constraint "nn" of relation "ne_nn_tbl"
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn NOT ENFORCED; --error
+ERROR: cannot alter enforceability of constraint "nn" of relation "ne_nn_tbl"
+ALTER TABLE ne_nn_tbl VALIDATE CONSTRAINT nn; --error
+ERROR: cannot validate NOT ENFORCED constraint
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT ENFORCED; --error
+ERROR: cannot create not-null constraint "nn_enforced1" on column "x" of table "ne_nn_tbl"
+DETAIL: A not-null constraint named "nn" already exists for this column.
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT ENFORCED; --no-op
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT VALID NOT ENFORCED; --no-op, because NOT ENFORCED imply NOT VALID.
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT ENFORCED NO INHERIT; --error
+ERROR: cannot change NO INHERIT status of NOT NULL constraint "nn" on relation "ne_nn_tbl"
+HINT: You might need to make the existing constraint inheritable using ALTER TABLE ... ALTER CONSTRAINT ... INHERIT.
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT VALID; --error, because NOT VALID imply ENFORCED
+ERROR: cannot change not enforced NOT NULL constraint "nn" on relation "ne_nn_tbl" to enforced
+HINT: You might need to ensure the existing constraint is enforced.
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x ENFORCED; --error
+ERROR: cannot change not enforced NOT NULL constraint "nn" on relation "ne_nn_tbl" to enforced
+HINT: You might need to ensure the existing constraint is enforced.
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x; --error, one column can only one not-null
+ERROR: cannot change not enforced NOT NULL constraint "nn" on relation "ne_nn_tbl" to enforced
+HINT: You might need to ensure the existing constraint is enforced.
+ALTER TABLE ne_nn_tbl ALTER COLUMN x SET NOT NULL; --error
+ERROR: cannot validate NOT ENFORCED constraint "nn" on relation "ne_nn_tbl"
+\d+ ne_nn_tbl
+ Table "public.ne_nn_tbl"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ x | integer | | | | plain | |
+Not-null constraints:
+ "nn" NOT NULL "x" NOT ENFORCED
+
+TRUNCATE ne_nn_tbl;
+--error, cannot use not enforced not-null constaint for primary key
+ALTER TABLE ne_nn_tbl ADD PRIMARY KEY(x);
+ERROR: cannot create primary key on column "x"
+DETAIL: The constraint "nn" on column "x" of table "ne_nn_tbl", marked NOT ENFORCED, is incompatible with a primary key.
+HINT: You might need to ensure the existing constraint is enforced.
+ALTER TABLE ne_nn_tbl ADD column y int NOT NULL NOT ENFORCED ENFORCED; --error
+ERROR: multiple ENFORCED/NOT ENFORCED clauses not allowed
+LINE 1: ...E ne_nn_tbl ADD column y int NOT NULL NOT ENFORCED ENFORCED;
+ ^
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn NO INHERIT; --ok
+ALTER TABLE ne_nn_tbl ADD column x1 int NOT NULL NOT ENFORCED, ADD column y int NOT NULL ENFORCED; --ok
+\d+ ne_nn_tbl
+ Table "public.ne_nn_tbl"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ x | integer | | | | plain | |
+ x1 | integer | | | | plain | |
+ y | integer | | not null | | plain | |
+Not-null constraints:
+ "nn" NOT NULL "x" NO INHERIT NOT ENFORCED
+ "ne_nn_tbl_x1_not_null" NOT NULL "x1" NOT ENFORCED
+ "ne_nn_tbl_y_not_null" NOT NULL "y"
+
-- NOT NULL NO INHERIT
CREATE TABLE ATACC1 (a int, NOT NULL a NO INHERIT);
CREATE TABLE ATACC2 () INHERITS (ATACC1);
@@ -1680,6 +1786,66 @@ COMMENT ON CONSTRAINT constr_parent2_a_not_null ON constr_parent2 IS 'this const
COMMENT ON CONSTRAINT constr_parent2_a_not_null ON constr_child2 IS 'this constraint is valid';
DEALLOCATE get_nnconstraint_info;
-- end NOT NULL NOT VALID
+-- Verify NOT NULL ENFORCED / ENFORCED with partition table.
+PREPARE get_nnconstraint_info(regclass[]) AS
+SELECT conrelid::regclass as relname, conname, convalidated, conislocal, coninhcount, conenforced
+FROM pg_constraint
+WHERE conrelid = ANY($1)
+ORDER BY conrelid::regclass::text COLLATE "C", conname;
+-- partitioned table have enforced not-null, then partitions cannot have not enforced not-null
+CREATE TABLE pp_nn (a int, b int, NOT NULL a) PARTITION BY LIST (a);
+CREATE TABLE pp_nn_1(a int, b int, CONSTRAINT nn1 NOT NULL a NOT ENFORCED);
+ALTER TABLE pp_nn ATTACH PARTITION pp_nn_1 FOR VALUES IN (NULL,5); --error
+ERROR: constraint "nn1" conflicts with NOT ENFORCED constraint on child table "pp_nn_1"
+DROP TABLE pp_nn, pp_nn_1;
+CREATE TABLE notnull_tbl1 (a int, b int, CONSTRAINT nn0 NOT NULL a NOT ENFORCED) PARTITION BY LIST (a);
+CREATE TABLE notnull_tbl1_1 PARTITION OF notnull_tbl1 FOR VALUES IN (1,2);
+-- if partitioned table not-null is not enforced, then partitions can have enforced
+-- or not enforced not-null
+CREATE TABLE notnull_tbl1_2(a int, CONSTRAINT nn1 NOT NULL a, b int);
+ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_2 FOR VALUES IN (3,4); --ok
+CREATE TABLE notnull_tbl1_3(a int, b int, CONSTRAINT nn2 NOT NULL a NOT ENFORCED);
+ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_3 FOR VALUES IN (NULL,5);
+CREATE TABLE notnull_tbl1_4(a int, b int);
+ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_4 FOR VALUES IN (6); --error
+ERROR: column "a" in child table "notnull_tbl1_4" must be marked NOT NULL NOT ENFORCED
+EXECUTE get_nnconstraint_info('{notnull_tbl1, notnull_tbl1_1, notnull_tbl1_2, notnull_tbl1_3}');
+ relname | conname | convalidated | conislocal | coninhcount | conenforced
+----------------+---------+--------------+------------+-------------+-------------
+ notnull_tbl1 | nn0 | f | t | 0 | f
+ notnull_tbl1_1 | nn0 | f | f | 1 | f
+ notnull_tbl1_2 | nn1 | t | f | 1 | t
+ notnull_tbl1_3 | nn2 | f | f | 1 | f
+(4 rows)
+
+ALTER TABLE notnull_tbl1 ALTER COLUMN a SET NOT NULL; --error, cannot validate not-enforced
+ERROR: cannot validate NOT ENFORCED constraint "nn0" on relation "notnull_tbl1"
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn0; --error, cannot validate not-enforced
+ERROR: cannot validate NOT ENFORCED constraint
+DROP TABLE notnull_tbl1, notnull_tbl1_4;
+-- Create table with NOT NULL NOT ENFORCED constraint, for pg_upgrade.
+CREATE TABLE nn_notenforced (a int, b int);
+INSERT INTO nn_notenforced VALUES (NULL, 1), (NULL, 2), (300, 3);
+ALTER TABLE nn_notenforced ADD CONSTRAINT nn NOT NULL a NOT ENFORCED;
+EXECUTE get_nnconstraint_info('{nn_notenforced}');
+ relname | conname | convalidated | conislocal | coninhcount | conenforced
+----------------+---------+--------------+------------+-------------+-------------
+ nn_notenforced | nn | f | t | 0 | f
+(1 row)
+
+-- Inherit test for pg_upgrade
+CREATE TABLE notenforced_nn_parent (a int);
+CREATE TABLE notenforced_nn_child () INHERITS (notenforced_nn_parent);
+ALTER TABLE notenforced_nn_parent ADD CONSTRAINT nn NOT NULL a NOT ENFORCED;
+EXECUTE get_nnconstraint_info('{notenforced_nn_parent, notenforced_nn_child}');
+ relname | conname | convalidated | conislocal | coninhcount | conenforced
+-----------------------+---------+--------------+------------+-------------+-------------
+ notenforced_nn_child | nn | f | f | 1 | f
+ notenforced_nn_parent | nn | f | t | 0 | f
+(2 rows)
+
+DEALLOCATE get_nnconstraint_info;
+--end of NOT NULL ENFORCED / ENFORCED with partition table.
-- Comments
-- Setup a low-level role to enforce non-superuser checks.
CREATE ROLE regress_constraint_comments;
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index d3c35c14847..36afb096fde 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -317,6 +317,28 @@ Referenced by:
TABLE "inhz" CONSTRAINT "inhz_x_fkey" FOREIGN KEY (x) REFERENCES inhz(xx)
DROP TABLE inhz;
+--not null not enforced constraint
+CREATE TABLE not_enforced_nn (a text, CONSTRAINT nn NOT NULL a NOT ENFORCED);
+COMMENT ON CONSTRAINT nn ON not_enforced_nn is 'not enforced not null constraint comment test';
+CREATE TABLE not_enforced_nn_copy(LIKE not_enforced_nn INCLUDING CONSTRAINTS INCLUDING COMMENTS);
+\d+ not_enforced_nn_copy
+ Table "public.not_enforced_nn_copy"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------+-----------+----------+---------+----------+--------------+-------------
+ a | text | | | | extended | |
+Not-null constraints:
+ "nn" NOT NULL "a" NOT ENFORCED
+
+SELECT conname, description
+FROM pg_description, pg_constraint c
+WHERE classoid = 'pg_constraint'::regclass
+AND objoid = c.oid AND c.conrelid = 'not_enforced_nn_copy'::regclass
+ORDER BY conname COLLATE "C";
+ conname | description
+---------+-----------------------------------------------
+ nn | not enforced not null constraint comment test
+(1 row)
+
-- including storage and comments
CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) ENFORCED PRIMARY KEY,
b text CHECK (length(b) > 100) NOT ENFORCED);
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 0490a746555..c15463f3df2 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1442,6 +1442,136 @@ alter table p1_c1 inherit p1;
ERROR: constraint "p1_a_check" conflicts with NOT ENFORCED constraint on child table "p1_c1"
drop table p1, p1_c1;
--
+-- Similarly, check the merging of existing constraints; a parent not-null constraint
+-- marked as NOT ENFORCED can merge with an ENFORCED child constraint, but the
+-- reverse is not allowed.
+--
+create table p1(f1 int constraint p1_a_nn not null);
+create table p1_c1(f1 int constraint p1_c1_nn not null not enforced);
+alter table p1_c1 inherit p1; --error
+ERROR: constraint "p1_c1_nn" conflicts with NOT ENFORCED constraint on child table "p1_c1"
+create table p1_c2(f1 int not null not enforced) inherits(p1); --error
+NOTICE: merging column "f1" with inherited definition
+ERROR: cannot define not-null constraint with NOT ENFORCED on column "f1"
+DETAIL: The column has an inherited ENFORCED not-null constraint.
+create table p1_c2(f1 int not null not enforced) inherits(p1_c1); --ok
+NOTICE: merging column "f1" with inherited definition
+select conenforced from pg_constraint where conrelid::regclass::text = ANY ('{p1_c2}') and contype = 'n';
+ conenforced
+-------------
+ f
+(1 row)
+
+drop table if exists p1, p1_c1, p1_c2;
+create table p1(f1 int constraint p1_a_nn not null not enforced);
+create table p1_c1(f1 int constraint p1_c1_a_nn not null);
+alter table p1_c1 inherit p1; --it's ok for parent not-null is not enforced while child is enforced
+create table p1_c2() inherits(p1, p1_c1); --merged multiple not-null constraints produce an enforced one
+NOTICE: merging multiple inherited definitions of column "f1"
+\d+ p1_c2
+ Table "public.p1_c2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+Not-null constraints:
+ "p1_a_nn" NOT NULL "f1" (inherited)
+Inherits: p1,
+ p1_c1
+
+create table p1_c3(f1 int);
+alter table p1_c3 inherit p1; --error, because p1_c3 does not have not-null constraint
+ERROR: column "f1" in child table "p1_c3" must be marked NOT NULL NOT ENFORCED
+create table p1_c4(f1 int not null not enforced) inherits(p1, p1_c1); --error, parent (p1_c1) have enforced
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging column "f1" with inherited definition
+ERROR: cannot define not-null constraint with NOT ENFORCED on column "f1"
+DETAIL: The column has an inherited ENFORCED not-null constraint.
+--merged multiple not-null constraints produce an enforced one, below two will be success.
+create table p1_c4(f1 int not null) inherits(p1, p1_c1);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging column "f1" with inherited definition
+create table p1_c5(f1 int) inherits(p1, p1_c1);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging column "f1" with inherited definition
+select conrelid::regclass, conname, conenforced, convalidated, coninhcount
+from pg_constraint
+where conrelid::regclass::text = ANY ('{p1, p1_c1, p1_c2, p1_c4, p1_c5}') and contype = 'n'
+order by conname, conrelid::regclass::text collate "C";
+ conrelid | conname | conenforced | convalidated | coninhcount
+----------+-------------------+-------------+--------------+-------------
+ p1 | p1_a_nn | f | f | 0
+ p1_c2 | p1_a_nn | t | t | 2
+ p1_c5 | p1_a_nn | t | t | 2
+ p1_c1 | p1_c1_a_nn | t | t | 1
+ p1_c4 | p1_c4_f1_not_null | t | t | 2
+(5 rows)
+
+drop table if exists p1 cascade;
+NOTICE: drop cascades to 4 other objects
+DETAIL: drop cascades to table p1_c1
+drop cascades to table p1_c2
+drop cascades to table p1_c4
+drop cascades to table p1_c5
+create table p1(f1 int);
+create table p1_c1() inherits(p1);
+alter table p1 add constraint p1_nn_1 not null f1 not enforced;
+alter table p1_c1 add constraint p1_nn_1 not null f1 enforced; --error, column f1 already have not-enforced
+ERROR: cannot change not enforced NOT NULL constraint "p1_nn_1" on relation "p1_c1" to enforced
+HINT: You might need to ensure the existing constraint is enforced.
+-- not allowed: child is not enforced, parent is enforced
+alter table p1 alter column f1 drop not null;
+alter table p1_c1 add constraint nn_x not null f1 not enforced;
+alter table p1 add constraint nn not null f1 enforced; --error
+ERROR: cannot change not enforced NOT NULL constraint "nn_x" on relation "p1_c1" to enforced
+HINT: You might need to ensure the existing constraint is enforced.
+alter table p1_c1 alter column f1 drop not null;
+alter table p1_c1 add constraint nn_v not null f1 not valid enforced;
+alter table p1 add constraint nn not null f1 not enforced; --error
+ERROR: cannot change enforced NOT NULL constraint "nn_v" on relation "p1_c1" to not enforced
+HINT: You might need to ensure the existing constraint is not enforced.
+drop table p1 cascade;
+NOTICE: drop cascades to table p1_c1
+-- Test ALTER CONSTRAINT INHERIT for not enforced not null
+create table inh_nn1 (f1 int, constraint nn not null f1 not enforced no inherit);
+create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
+create table inh_nn3 (f1 int) inherits (inh_nn1, inh_nn2);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging column "f1" with inherited definition
+create table inh_nn4 (f1 int) inherits (inh_nn1, inh_nn2, inh_nn3);
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f1"
+NOTICE: merging multiple inherited definitions of column "f2"
+NOTICE: merging multiple inherited definitions of column "f3"
+NOTICE: merging column "f1" with inherited definition
+alter table inh_nn2 add constraint nn2 not null f1;
+alter table inh_nn1 alter constraint nn inherit;
+select conrelid::regclass, conname, conkey[1], conenforced, convalidated, coninhcount, connoinherit, conislocal
+from pg_constraint
+where conrelid::regclass::text = ANY ('{inh_nn1, inh_nn2, inh_nn3, inh_nn4}')
+and contype = 'n'
+order by conname, conrelid::regclass::text collate "C";
+ conrelid | conname | conkey | conenforced | convalidated | coninhcount | connoinherit | conislocal
+----------+---------+--------+-------------+--------------+-------------+--------------+------------
+ inh_nn1 | nn | 1 | f | f | 0 | f | t
+ inh_nn2 | nn2 | 1 | t | t | 1 | f | t
+ inh_nn3 | nn2 | 1 | t | t | 2 | f | f
+ inh_nn4 | nn2 | 1 | t | t | 3 | f | f
+(4 rows)
+
+drop table inh_nn1 cascade;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table inh_nn2
+drop cascades to table inh_nn3
+drop cascades to table inh_nn4
+create table inh_nn1 (f1 int, constraint nn not null f1 no inherit);
+create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
+alter table inh_nn2 add constraint nn2 not null f1 not enforced;
+--error, parent not-null is enforcecd, child not-null cannot be not enforced
+alter table inh_nn1 alter constraint nn inherit;
+ERROR: cannot validate NOT ENFORCED constraint "nn2" on relation "inh_nn2"
+drop table inh_nn1 cascade;
+NOTICE: drop cascades to table inh_nn2
+--
-- Test DROP behavior of multiply-defined CHECK constraints
--
create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index b7f6efdd814..98b0d4e4322 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -668,22 +668,70 @@ drop table notnull_tbl2, notnull_tbl3, notnull_tbl4, notnull_tbl5, notnull_tbl6;
-- error cases:
create table notnull_tbl_fail (a serial constraint foo not null constraint bar not null);
+create table notnull_tbl_fail (a serial constraint foo not null constraint bar not null not enforced);
create table notnull_tbl_fail (a serial constraint foo not null no inherit constraint foo not null);
+create table notnull_tbl_fail (a serial constraint foo not null not enforced);
create table notnull_tbl_fail (a int constraint foo not null, constraint foo not null a no inherit);
+create table notnull_tbl_fail (a int constraint foo not null, constraint foo not null a not enforced);
create table notnull_tbl_fail (a serial constraint foo not null, constraint bar not null a);
+create table notnull_tbl_fail (a serial constraint foo not null, constraint bar not null a not enforced);
create table notnull_tbl_fail (a serial, constraint foo not null a, constraint bar not null a);
+create table notnull_tbl_fail (a serial, constraint foo not null a, constraint bar not null a not enforced);
create table notnull_tbl_fail (a serial, constraint foo not null a no inherit);
+create table notnull_tbl_fail (a serial, constraint foo not null a not enforced);
create table notnull_tbl_fail (a serial not null no inherit);
+create table notnull_tbl_fail (a serial not null not enforced);
create table notnull_tbl_fail (like notnull_tbl1, constraint foo2 not null a);
+create table notnull_tbl_fail (like notnull_tbl1, constraint foo2 not null a not enforced);
create table notnull_tbl_fail (a int primary key constraint foo not null no inherit);
+create table notnull_tbl_fail (a int primary key constraint foo not null not enforced);
create table notnull_tbl_fail (a int not null no inherit primary key);
create table notnull_tbl_fail (a int primary key, not null a no inherit);
+create table notnull_tbl_fail (a int primary key, not null a not enforced);
create table notnull_tbl_fail (a int, primary key(a), not null a no inherit);
+create table notnull_tbl_fail (a int, primary key(a), not null a not enforced);
create table notnull_tbl_fail (a int generated by default as identity, constraint foo not null a no inherit);
+create table notnull_tbl_fail (a int generated by default as identity, constraint foo not null a not enforced);
create table notnull_tbl_fail (a int generated by default as identity not null no inherit);
+create table notnull_tbl_fail (a int generated by default as identity not null not enforced);
+alter table notnull_tbl1 add column b int not null not enforced; --ok
+alter table notnull_tbl1 alter column b add generated always as identity;
+alter table notnull_tbl1 add column c int not null not enforced, alter column c add generated always as identity;
+alter table notnull_tbl1 add column c int generated always as identity not null not enforced;
+alter table notnull_tbl1 add column c serial not null not enforced;
+alter table notnull_tbl1 add column c serial, add constraint notnull_tbl1_c_not_null not null c not enforced;
drop table notnull_tbl1;
+-- NOT NULL [NOT] ENFORCED
+CREATE TABLE ne_nn_tbl (x int, CONSTRAINT nn NOT NULL x NOT ENFORCED, NOT NULL x ENFORCED); --error
+CREATE TABLE ne_nn_tbl (x int, CONSTRAINT nn NOT NULL x NOT ENFORCED);
+
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'nn';
+INSERT INTO ne_nn_tbl VALUES (NULL); --ok
+
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn ENFORCED; --error
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn NOT ENFORCED; --error
+ALTER TABLE ne_nn_tbl VALIDATE CONSTRAINT nn; --error
+
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT ENFORCED; --error
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT ENFORCED; --no-op
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT VALID NOT ENFORCED; --no-op, because NOT ENFORCED imply NOT VALID.
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT ENFORCED NO INHERIT; --error
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT VALID; --error, because NOT VALID imply ENFORCED
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x ENFORCED; --error
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x; --error, one column can only one not-null
+ALTER TABLE ne_nn_tbl ALTER COLUMN x SET NOT NULL; --error
+\d+ ne_nn_tbl
+
+TRUNCATE ne_nn_tbl;
+--error, cannot use not enforced not-null constaint for primary key
+ALTER TABLE ne_nn_tbl ADD PRIMARY KEY(x);
+ALTER TABLE ne_nn_tbl ADD column y int NOT NULL NOT ENFORCED ENFORCED; --error
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn NO INHERIT; --ok
+ALTER TABLE ne_nn_tbl ADD column x1 int NOT NULL NOT ENFORCED, ADD column y int NOT NULL ENFORCED; --ok
+\d+ ne_nn_tbl
+
-- NOT NULL NO INHERIT
CREATE TABLE ATACC1 (a int, NOT NULL a NO INHERIT);
CREATE TABLE ATACC2 () INHERITS (ATACC1);
@@ -1016,6 +1064,49 @@ DEALLOCATE get_nnconstraint_info;
-- end NOT NULL NOT VALID
+-- Verify NOT NULL ENFORCED / ENFORCED with partition table.
+PREPARE get_nnconstraint_info(regclass[]) AS
+SELECT conrelid::regclass as relname, conname, convalidated, conislocal, coninhcount, conenforced
+FROM pg_constraint
+WHERE conrelid = ANY($1)
+ORDER BY conrelid::regclass::text COLLATE "C", conname;
+
+-- partitioned table have enforced not-null, then partitions cannot have not enforced not-null
+CREATE TABLE pp_nn (a int, b int, NOT NULL a) PARTITION BY LIST (a);
+CREATE TABLE pp_nn_1(a int, b int, CONSTRAINT nn1 NOT NULL a NOT ENFORCED);
+ALTER TABLE pp_nn ATTACH PARTITION pp_nn_1 FOR VALUES IN (NULL,5); --error
+DROP TABLE pp_nn, pp_nn_1;
+
+CREATE TABLE notnull_tbl1 (a int, b int, CONSTRAINT nn0 NOT NULL a NOT ENFORCED) PARTITION BY LIST (a);
+CREATE TABLE notnull_tbl1_1 PARTITION OF notnull_tbl1 FOR VALUES IN (1,2);
+-- if partitioned table not-null is not enforced, then partitions can have enforced
+-- or not enforced not-null
+CREATE TABLE notnull_tbl1_2(a int, CONSTRAINT nn1 NOT NULL a, b int);
+ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_2 FOR VALUES IN (3,4); --ok
+CREATE TABLE notnull_tbl1_3(a int, b int, CONSTRAINT nn2 NOT NULL a NOT ENFORCED);
+ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_3 FOR VALUES IN (NULL,5);
+CREATE TABLE notnull_tbl1_4(a int, b int);
+ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_4 FOR VALUES IN (6); --error
+
+EXECUTE get_nnconstraint_info('{notnull_tbl1, notnull_tbl1_1, notnull_tbl1_2, notnull_tbl1_3}');
+ALTER TABLE notnull_tbl1 ALTER COLUMN a SET NOT NULL; --error, cannot validate not-enforced
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn0; --error, cannot validate not-enforced
+DROP TABLE notnull_tbl1, notnull_tbl1_4;
+
+-- Create table with NOT NULL NOT ENFORCED constraint, for pg_upgrade.
+CREATE TABLE nn_notenforced (a int, b int);
+INSERT INTO nn_notenforced VALUES (NULL, 1), (NULL, 2), (300, 3);
+ALTER TABLE nn_notenforced ADD CONSTRAINT nn NOT NULL a NOT ENFORCED;
+EXECUTE get_nnconstraint_info('{nn_notenforced}');
+
+-- Inherit test for pg_upgrade
+CREATE TABLE notenforced_nn_parent (a int);
+CREATE TABLE notenforced_nn_child () INHERITS (notenforced_nn_parent);
+ALTER TABLE notenforced_nn_parent ADD CONSTRAINT nn NOT NULL a NOT ENFORCED;
+EXECUTE get_nnconstraint_info('{notenforced_nn_parent, notenforced_nn_child}');
+DEALLOCATE get_nnconstraint_info;
+--end of NOT NULL ENFORCED / ENFORCED with partition table.
+
-- Comments
-- Setup a low-level role to enforce non-superuser checks.
CREATE ROLE regress_constraint_comments;
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index 93389b57dbf..59cf66edd5d 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -127,6 +127,18 @@ CREATE TABLE inhz (x text REFERENCES inhz, LIKE inhx INCLUDING INDEXES);
\d inhz
DROP TABLE inhz;
+--not null not enforced constraint
+CREATE TABLE not_enforced_nn (a text, CONSTRAINT nn NOT NULL a NOT ENFORCED);
+COMMENT ON CONSTRAINT nn ON not_enforced_nn is 'not enforced not null constraint comment test';
+CREATE TABLE not_enforced_nn_copy(LIKE not_enforced_nn INCLUDING CONSTRAINTS INCLUDING COMMENTS);
+\d+ not_enforced_nn_copy
+
+SELECT conname, description
+FROM pg_description, pg_constraint c
+WHERE classoid = 'pg_constraint'::regclass
+AND objoid = c.oid AND c.conrelid = 'not_enforced_nn_copy'::regclass
+ORDER BY conname COLLATE "C";
+
-- including storage and comments
CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) ENFORCED PRIMARY KEY,
b text CHECK (length(b) > 100) NOT ENFORCED);
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 699e8ac09c8..ce4ae85c425 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -527,6 +527,76 @@ create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
alter table p1_c1 inherit p1;
drop table p1, p1_c1;
+--
+-- Similarly, check the merging of existing constraints; a parent not-null constraint
+-- marked as NOT ENFORCED can merge with an ENFORCED child constraint, but the
+-- reverse is not allowed.
+--
+create table p1(f1 int constraint p1_a_nn not null);
+create table p1_c1(f1 int constraint p1_c1_nn not null not enforced);
+alter table p1_c1 inherit p1; --error
+create table p1_c2(f1 int not null not enforced) inherits(p1); --error
+create table p1_c2(f1 int not null not enforced) inherits(p1_c1); --ok
+select conenforced from pg_constraint where conrelid::regclass::text = ANY ('{p1_c2}') and contype = 'n';
+drop table if exists p1, p1_c1, p1_c2;
+
+
+create table p1(f1 int constraint p1_a_nn not null not enforced);
+create table p1_c1(f1 int constraint p1_c1_a_nn not null);
+alter table p1_c1 inherit p1; --it's ok for parent not-null is not enforced while child is enforced
+create table p1_c2() inherits(p1, p1_c1); --merged multiple not-null constraints produce an enforced one
+\d+ p1_c2
+create table p1_c3(f1 int);
+alter table p1_c3 inherit p1; --error, because p1_c3 does not have not-null constraint
+create table p1_c4(f1 int not null not enforced) inherits(p1, p1_c1); --error, parent (p1_c1) have enforced
+--merged multiple not-null constraints produce an enforced one, below two will be success.
+create table p1_c4(f1 int not null) inherits(p1, p1_c1);
+create table p1_c5(f1 int) inherits(p1, p1_c1);
+select conrelid::regclass, conname, conenforced, convalidated, coninhcount
+from pg_constraint
+where conrelid::regclass::text = ANY ('{p1, p1_c1, p1_c2, p1_c4, p1_c5}') and contype = 'n'
+order by conname, conrelid::regclass::text collate "C";
+drop table if exists p1 cascade;
+
+
+create table p1(f1 int);
+create table p1_c1() inherits(p1);
+alter table p1 add constraint p1_nn_1 not null f1 not enforced;
+alter table p1_c1 add constraint p1_nn_1 not null f1 enforced; --error, column f1 already have not-enforced
+
+-- not allowed: child is not enforced, parent is enforced
+alter table p1 alter column f1 drop not null;
+alter table p1_c1 add constraint nn_x not null f1 not enforced;
+alter table p1 add constraint nn not null f1 enforced; --error
+
+alter table p1_c1 alter column f1 drop not null;
+alter table p1_c1 add constraint nn_v not null f1 not valid enforced;
+alter table p1 add constraint nn not null f1 not enforced; --error
+drop table p1 cascade;
+
+
+-- Test ALTER CONSTRAINT INHERIT for not enforced not null
+create table inh_nn1 (f1 int, constraint nn not null f1 not enforced no inherit);
+create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
+create table inh_nn3 (f1 int) inherits (inh_nn1, inh_nn2);
+create table inh_nn4 (f1 int) inherits (inh_nn1, inh_nn2, inh_nn3);
+alter table inh_nn2 add constraint nn2 not null f1;
+alter table inh_nn1 alter constraint nn inherit;
+
+select conrelid::regclass, conname, conkey[1], conenforced, convalidated, coninhcount, connoinherit, conislocal
+from pg_constraint
+where conrelid::regclass::text = ANY ('{inh_nn1, inh_nn2, inh_nn3, inh_nn4}')
+and contype = 'n'
+order by conname, conrelid::regclass::text collate "C";
+
+drop table inh_nn1 cascade;
+create table inh_nn1 (f1 int, constraint nn not null f1 no inherit);
+create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
+alter table inh_nn2 add constraint nn2 not null f1 not enforced;
+--error, parent not-null is enforcecd, child not-null cannot be not enforced
+alter table inh_nn1 alter constraint nn inherit;
+drop table inh_nn1 cascade;
+
--
-- Test DROP behavior of multiply-defined CHECK constraints
--
--
2.34.1
