Subject: NOT NULL NOT ENFORCED

From: jian he <[email-masked]>
Date: Thu, 4 Sep 2025 10:56:37 +0800

hi.

attached patch will remove sql_features.txt item F492: "Optional table
constraint
enforcement" Comment: "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, can not validate
not enforced not-null constraint
  ALTER TABLE ADD NOT NULL: error out, can not 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.

I have written extensive tests to cover the corner case, the tests may be
overwhelming.

[1]: https://www.postgresql.org/docs/devel/features-sql-standard.html
From 12e06cc4c964b6c9c17c55dc94edac0a18653f2f Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Thu, 4 Sep 2025 10:52:30 +0800
Subject: [PATCH v1 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, can not validate not
  enforced not-null constraint
  ALTER TABLE ADD NOT NULL: error out, can not 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.

reference:  https://git.postgresql.org/cgit/postgresql.git/commit/?id=a379061a22a8fdf421e1a457cc6af8503def6252
discussion: https://postgr.es/m/

[1]: https://www.postgresql.org/docs/devel/features-sql-standard.html
---
 doc/src/sgml/ref/alter_table.sgml             |   4 +-
 doc/src/sgml/ref/create_table.sgml            |   4 +-
 src/backend/catalog/heap.c                    |  61 +++++--
 src/backend/catalog/pg_constraint.c           |  38 ++--
 src/backend/catalog/sql_features.txt          |   2 +-
 src/backend/commands/tablecmds.c              | 129 +++++++++++---
 src/backend/parser/gram.y                     |   2 +-
 src/backend/parser/parse_utilcmd.c            |  80 ++++++---
 src/backend/utils/cache/relcache.c            |   2 +-
 src/bin/psql/describe.c                       |  20 ++-
 src/include/catalog/heap.h                    |   2 +-
 src/include/catalog/pg_constraint.h           |   4 +-
 src/test/regress/expected/constraints.out     | 162 ++++++++++++++++++
 .../regress/expected/create_table_like.out    |  22 +++
 src/test/regress/expected/inherit.out         | 107 ++++++++++++
 src/test/regress/sql/constraints.sql          |  88 ++++++++++
 src/test/regress/sql/create_table_like.sql    |  12 ++
 src/test/regress/sql/inherit.sql              |  62 +++++++
 18 files changed, 715 insertions(+), 86 deletions(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 8867da6c693..e8a93c97432 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1481,8 +1481,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 dc000e913c1..4d0d0e9cbf4 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 fd6537567ea..2a8f4c42bac 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -2252,8 +2252,8 @@ 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_enforced, bool is_validated, bool is_local,
+				int inhcount, bool is_no_inherit)
 {
 	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,16 +2627,18 @@ 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 or enforceability.
 			 */
 			if (AdjustNotNullInheritance(RelationGetRelid(rel), colnum,
 										 is_local, cdef->is_no_inherit,
-										 cdef->skip_validation))
+										 cdef->skip_validation,
+										 cdef->is_enforced))
 				continue;
 
 			/*
@@ -2664,6 +2666,7 @@ AddRelationNewConstraints(Relation rel,
 
 			constrOid =
 				StoreRelNotNull(rel, nnname, colnum,
+								cdef->is_enforced,
 								cdef->initially_valid,
 								is_local,
 								inhcount,
@@ -2675,7 +2678,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;
@@ -2949,7 +2952,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);)
 		{
@@ -2965,6 +2968,11 @@ 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.
@@ -3010,6 +3018,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);
 			}
@@ -3044,11 +3063,14 @@ 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);
+		StoreRelNotNull(rel, conname, attnum,
+						constr->is_enforced,
+						!constr->skip_validation,
+						true, inhcount, constr->is_no_inherit);
 
-		nncols = lappend_int(nncols, attnum);
+		if (constr->is_enforced)
+			nncols = lappend_int(nncols, attnum);
 	}
 
 	/*
@@ -3093,6 +3115,16 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
 					conname = other->name;
 
 				inhcount++;
+
+				/*
+				 * if a column inherit multiple not-null constraints, the
+				 * enforced status should the same.
+				*/
+				if (other->is_enforced != cooked->is_enforced)
+					ereport(ERROR,
+							errcode(ERRCODE_DATATYPE_MISMATCH),
+							errmsg("cannot define not-null constraint on column \"%s\"", conname),
+							errdetail("The column inherited not-null constraints have conflict ENFORCED status."));
 				old_notnulls = list_delete_nth_cell(old_notnulls, restpos);
 			}
 			else
@@ -3123,10 +3155,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,
+		StoreRelNotNull(rel, conname, cooked->attnum,
+						cooked->is_enforced,
+						cooked->is_enforced ? true : false,
 						false, inhcount, false);
 
-		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 6002fd0002f..a625813cd6f 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
@@ -738,8 +739,8 @@ extractNotNullColumn(HeapTuple constrTup)
  * it's already true; otherwise we increment coninhcount by 1.
  */
 bool
-AdjustNotNullInheritance(Oid relid, AttrNumber attnum,
-						 bool is_local, bool is_no_inherit, bool is_notvalid)
+AdjustNotNullInheritance(Oid relid, AttrNumber attnum, bool is_local,
+						 bool is_no_inherit, bool is_notvalid, bool is_enforced)
 {
 	HeapTuple	tup;
 
@@ -769,7 +770,7 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum,
 		 * 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\"",
@@ -777,6 +778,18 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum,
 					errhint("You might need to validate it using %s.",
 							"ALTER TABLE ... VALIDATE CONSTRAINT"));
 
+		/*
+		 * If the ENFORCED status we're asked for doesn't match what the
+		 * existing constraint has, throw an error.
+		 */
+		if (is_enforced != conform->conenforced)
+			ereport(ERROR,
+					errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+					errmsg("cannot change ENFORCED status of NOT NULL constraint \"%s\" on relation \"%s\"",
+						   NameStr(conform->conname), get_rel_name(relid)),
+					errhint("You might need to drop the existing not enforced constraint using %s.",
+							"ALTER TABLE ... DROP CONSTRAINT"));
+
 		if (!is_local)
 		{
 			if (pg_add_s16_overflow(conform->coninhcount, 1,
@@ -807,6 +820,7 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum,
  * 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.
@@ -853,7 +867,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;
@@ -873,7 +887,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 = true;
 			constr->is_no_inherit = conForm->connoinherit;
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index ebe85337c28..c418e6cd1dd 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 082a3575d62..733fe103f7d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -2740,13 +2740,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
+		 * 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++)
@@ -7726,6 +7729,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,
@@ -7754,13 +7758,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,
@@ -7799,8 +7796,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
+		{
+			/* If the column is already nullable there's nothing to do. */
+			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,
@@ -7935,7 +7941,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. Note: it maybe not enforced! */
 	tuple = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum);
 	if (HeapTupleIsValid(tuple))
 	{
@@ -7971,7 +7977,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
 			conForm->conislocal = true;
 			changed = true;
 		}
-		else if (!conForm->convalidated)
+		else if (!conForm->convalidated && conForm->conenforced)
 		{
 			/*
 			 * Flip attnotnull and convalidated, and also validate the
@@ -7981,6 +7987,13 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
 											recurse, recursing, lockmode);
 		}
 
+		if (!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 (changed)
 		{
 			Relation	constr_rel;
@@ -9559,7 +9572,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),
@@ -9569,6 +9582,18 @@ 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 drop it first using %s.",
+						"ALTER TABLE ... DROP CONSTRAINT"));
 }
 
 /*
@@ -9937,9 +9962,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. For not enforced not-null, no need set attnotnull.
 		 */
-		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);
@@ -12584,6 +12609,7 @@ ATExecAlterConstrInheritability(List **wqueue, ATAlterConstraint *cmdcon,
 	AttrNumber	colNum;
 	char	   *colName;
 	List	   *children;
+	bool		notenforced;
 
 	Assert(cmdcon->alterInheritability);
 
@@ -12592,6 +12618,8 @@ ATExecAlterConstrInheritability(List **wqueue, ATAlterConstraint *cmdcon,
 	/* The current implementation only works for NOT NULL constraints */
 	Assert(currcon->contype == CONSTRAINT_NOTNULL);
 
+	notenforced = !currcon->conenforced;
+
 	/*
 	 * If called to modify a constraint that's already in the desired state,
 	 * silently do nothing.
@@ -12632,6 +12660,31 @@ ATExecAlterConstrInheritability(List **wqueue, ATAlterConstraint *cmdcon,
 			CatalogTupleUpdate(conrel, &childtup->t_self, childtup);
 			heap_freetuple(childtup);
 		}
+		else if (notenforced)
+		{
+			/*
+			 * We can't use ATExecSetNotNull here because it adds an enforced
+			 * not-null constraint, but here we only want a non-enforced one.
+			*/
+			Relation	childrel;
+			AlteredTableInfo *childtab;
+			Constraint *nnconstr;
+
+			childrel = table_open(childoid, NoLock);
+			CheckAlterTableIsSafe(childrel);
+
+			childtab = ATGetQueueEntry(wqueue, childrel);
+
+			nnconstr = makeNotNullConstraint(makeString(colName));
+			nnconstr->conname = pstrdup(NameStr(currcon->conname));
+			nnconstr->is_enforced = false;
+			nnconstr->initially_valid = false;
+			nnconstr->skip_validation = true;
+
+			ATAddCheckNNConstraint(wqueue, childtab, childrel, nnconstr,
+								   true, true, false, lockmode);
+			table_close(childrel, NoLock);
+		}
 		else
 		{
 			Relation	childrel = table_open(childoid, NoLock);
@@ -17505,9 +17558,28 @@ 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);
+
+					/*
+					 * If the child constraint is "not enforced" then cannot
+					 * merge with a enforced parent constraint
+					*/
+					if (parent_con->conenforced && !child_con->conenforced)
+						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,
@@ -17759,13 +17831,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 db43034b9db..c54131bd601 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4258,7 +4258,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 afcf54169c3..b939980c068 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -317,6 +317,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? */
@@ -713,6 +717,9 @@ 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)
 	{
@@ -773,6 +780,11 @@ 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.
@@ -792,6 +804,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)
@@ -1131,6 +1152,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);
@@ -1272,33 +1294,41 @@ 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.
+	*/
+	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);
 			}
 		}
 	}
@@ -3992,7 +4022,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"),
@@ -4009,7 +4040,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 6fe268a8eec..2333ef9b1fc 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4632,7 +4632,7 @@ CheckNNConstraintFetch(Relation relation)
 		 */
 		if (conform->contype == CONSTRAINT_NOTNULL)
 		{
-			if (!conform->convalidated)
+			if (!conform->convalidated && conform->conenforced)
 			{
 				AttrNumber	attnum;
 
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 4aa793d7de7..543ed309b92 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3108,7 +3108,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"
@@ -3132,15 +3139,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/catalog/heap.h b/src/include/catalog/heap.h
index dbd339e9df4..5e7fe1d2f69 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 4afceb5c692..fc581701c61 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -263,8 +263,8 @@ extern HeapTuple findNotNullConstraintAttnum(Oid relid, AttrNumber attnum);
 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,
-									 bool is_local, bool is_no_inherit, bool is_notvalid);
+extern bool AdjustNotNullInheritance(Oid relid, AttrNumber attnum, 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 3590d3274f0..2291c017605 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -961,33 +961,135 @@ 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 nn not null c not enforced;
+ERROR:  cannot change ENFORCED status of NOT NULL constraint "notnull_tbl1_c_not_null" on relation "notnull_tbl1"
+HINT:  You might need to drop the existing not enforced constraint using ALTER TABLE ... DROP CONSTRAINT.
 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);
+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
+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 ADD CONSTRAINT nn_enforced1 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_enforced1 NOT NULL x NOT VALID NOT ENFORCED; --no-op
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT ENFORCED;  --no-op
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT VALID; --error
+ERROR:  cannot change ENFORCED status of NOT NULL constraint "nn" on relation "ne_nn_tbl"
+HINT:  You might need to drop the existing not enforced constraint using ALTER TABLE ... DROP CONSTRAINT.
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced NOT NULL x ENFORCED; --error
+ERROR:  cannot change ENFORCED status of NOT NULL constraint "nn" on relation "ne_nn_tbl"
+HINT:  You might need to drop the existing not enforced constraint using ALTER TABLE ... DROP CONSTRAINT.
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced NOT NULL x; --error, one column can only one not-null
+ERROR:  cannot change ENFORCED status of NOT NULL constraint "nn" on relation "ne_nn_tbl"
+HINT:  You might need to drop the existing not enforced constraint using ALTER TABLE ... DROP CONSTRAINT.
+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, can not 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 drop it first using ALTER TABLE ... DROP CONSTRAINT.
+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 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" 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);
@@ -1668,6 +1770,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 can not 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, can not validate not-enforced
+ERROR:  cannot validate NOT ENFORCED constraint "nn0" on relation "notnull_tbl1"
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn0;   --error, can not 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 29a779c2e90..993fd2a6b3b 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 5b5055babdc..13008b71570 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1439,6 +1439,113 @@ 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.
+drop table if exists p1, p1_c1;
+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; --ok
+create table p1_c3() inherits(p1, p1_c1); --error
+NOTICE:  merging multiple inherited definitions of column "f1"
+ERROR:  cannot define not-null constraint on column "p1_a_nn"
+DETAIL:  The column inherited not-null constraints have conflict ENFORCED status.
+create table p1_c3(f1 int);
+alter table p1_c3 inherit p1; --error
+ERROR:  column "f1" in child table "p1_c3" must be marked NOT NULL NOT ENFORCED
+--error, can not merge conflict not-null constraint
+create table p1_c4(f1 int not null not enforced) inherits(p1, p1_c1);
+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.
+create table p1_c4(f1 int not null) inherits(p1, p1_c1); --ok
+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); --error
+NOTICE:  merging multiple inherited definitions of column "f1"
+NOTICE:  merging column "f1" with inherited definition
+ERROR:  cannot define not-null constraint on column "p1_a_nn"
+DETAIL:  The column inherited not-null constraints have conflict ENFORCED status.
+drop table if exists p1, p1_c3 cascade;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to table p1_c1
+drop cascades to table p1_c4
+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_c1_nn_1 not null f1 enforced; --error, f1 already have not-null constraint
+ERROR:  cannot change ENFORCED status of NOT NULL constraint "p1_nn_1" on relation "p1_c1"
+HINT:  You might need to drop the existing not enforced constraint using ALTER TABLE ... DROP CONSTRAINT.
+create table p1_nenn(f1 int constraint p1_nn not null) inherits(p1, p1_c1); --ok
+NOTICE:  merging multiple inherited definitions of column "f1"
+NOTICE:  merging column "f1" with inherited definition
+\d+ p1_nenn
+                                  Table "public.p1_nenn"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ f1     | integer |           | not null |         | plain   |              | 
+Not-null constraints:
+    "p1_nn" NOT NULL "f1" (local, inherited)
+Inherits: p1,
+          p1_c1
+
+drop table p1_nenn;
+-- not allowed: child is not enforced, parent have enforced
+alter table p1 alter column f1 drop not null;
+alter table p1_c1 add constraint nn not null f1 not enforced;
+alter table p1 add constraint nn not null f1 enforced; --error
+ERROR:  cannot change ENFORCED status of NOT NULL constraint "nn" on relation "p1_c1"
+HINT:  You might need to drop the existing not enforced constraint using ALTER TABLE ... DROP CONSTRAINT.
+alter table p1_c1 alter column f1 drop not null;
+alter table p1_c1 add constraint nn not null f1 not valid enforced;
+alter table p1 add constraint nn not null f1 not enforced; --error
+ERROR:  cannot change ENFORCED status of NOT NULL constraint "nn" on relation "p1_c1"
+HINT:  You might need to drop the existing not enforced constraint using ALTER TABLE ... DROP CONSTRAINT.
+drop table p1 cascade;
+NOTICE:  drop cascades to table p1_c1
+-- Test INHERIT for not enforced constraint.
+create table inh_nn1 (f1 int, constraint nn not null f1 no inherit not enforced);
+create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
+create table inh_nn3 () inherits (inh_nn2);
+create table inh_nn4 () inherits (inh_nn1);
+alter table inh_nn2 add constraint nn not null f1 not enforced;
+alter table inh_nn1 alter constraint nn inherit; --ok.
+select  conrelid::regclass, conname, contype, conenforced, convalidated, coninhcount, connoinherit, conislocal
+from    pg_constraint
+where   conrelid::regclass::text = ANY ('{inh_nn1, inh_nn2, inh_nn3, inh_nn4}')
+order by conname, conrelid::regclass::text collate "C";
+ conrelid | conname | contype | conenforced | convalidated | coninhcount | connoinherit | conislocal 
+----------+---------+---------+-------------+--------------+-------------+--------------+------------
+ inh_nn1  | nn      | n       | f           | f            |           0 | f            | t
+ inh_nn2  | nn      | n       | f           | f            |           1 | f            | t
+ inh_nn3  | nn      | n       | f           | f            |           1 | f            | f
+ inh_nn4  | nn      | n       | f           | f            |           1 | 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 not enforced);
+create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
+alter table inh_nn2 add constraint nn not null f1;
+alter table inh_nn1 alter constraint nn inherit; --error
+ERROR:  cannot change ENFORCED status of NOT NULL constraint "nn" on relation "inh_nn2"
+HINT:  You might need to drop the existing not enforced constraint using ALTER TABLE ... DROP CONSTRAINT.
+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 1f6dc8fd69f..2723c754774 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -663,22 +663,67 @@ 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 nn 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);
+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
+
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'nn';
+INSERT INTO ne_nn_tbl VALUES (NULL); --ok
+
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT ENFORCED NO INHERIT; --error
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT VALID NOT ENFORCED; --no-op
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT ENFORCED;  --no-op
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT VALID; --error
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced NOT NULL x ENFORCED; --error
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced 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, can not 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 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);
@@ -1008,6 +1053,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 can not 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, can not validate not-enforced
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn0;   --error, can not 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 bf8702116a7..d1a0613aa4d 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..afe6d048d38 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -527,6 +527,68 @@ 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
+drop table if exists p1, p1_c1;
+
+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; --ok
+create table p1_c3() inherits(p1, p1_c1); --error
+create table p1_c3(f1 int);
+alter table p1_c3 inherit p1; --error
+--error, can not merge conflict not-null constraint
+create table p1_c4(f1 int not null not enforced) inherits(p1, p1_c1);
+create table p1_c4(f1 int not null) inherits(p1, p1_c1); --ok
+create table p1_c5(f1 int) inherits(p1, p1_c1); --error
+drop table if exists p1, p1_c3 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_c1_nn_1 not null f1 enforced; --error, f1 already have not-null constraint
+create table p1_nenn(f1 int constraint p1_nn not null) inherits(p1, p1_c1); --ok
+\d+ p1_nenn
+drop table p1_nenn;
+
+-- not allowed: child is not enforced, parent have enforced
+alter table p1 alter column f1 drop not null;
+alter table p1_c1 add constraint nn 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 not null f1 not valid enforced;
+alter table p1 add constraint nn not null f1 not enforced; --error
+drop table p1 cascade;
+
+-- Test INHERIT for not enforced constraint.
+create table inh_nn1 (f1 int, constraint nn not null f1 no inherit not enforced);
+create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
+create table inh_nn3 () inherits (inh_nn2);
+create table inh_nn4 () inherits (inh_nn1);
+alter table inh_nn2 add constraint nn not null f1 not enforced;
+alter table inh_nn1 alter constraint nn inherit; --ok.
+
+select  conrelid::regclass, conname, contype, conenforced, convalidated, coninhcount, connoinherit, conislocal
+from    pg_constraint
+where   conrelid::regclass::text = ANY ('{inh_nn1, inh_nn2, inh_nn3, inh_nn4}')
+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 not enforced);
+create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
+alter table inh_nn2 add constraint nn not null f1;
+alter table inh_nn1 alter constraint nn inherit; --error
+drop table inh_nn1 cascade;
+
 --
 -- Test DROP behavior of multiply-defined CHECK constraints
 --
-- 
2.34.1

---

From: Álvaro Herrera <[email-masked]>
Date: Thu, 4 Sep 2025 14:00:29 +0200

>  				old_notnulls = list_delete_nth_cell(old_notnulls, restpos);

Hmmm, are you sure about this?   I think if a table has two parents, one
with enforced and the other with not enforced constraint, then it's okay
to get them combined resulting in one enforced constraint.

> +							"ALTER TABLE ... DROP CONSTRAINT"));

I think the hint here should suggest to make the existing constraint as
enforced, rather than drop it.

>  						   !constr->skip_validation);

Didn't we decide that attnotnull meant whether a constraint exists at
all, without making a judgement on whether it's enforced or valid?  The
important change should be in CheckNNConstraintFetch() which should
determine attnullability in a way that allows executor know whether the
column is nullable or not.  I admit we might want to handle this
differently for unenforced constraints, but we should discuss that and
make sure that's what we want.

> +			 * not-null constraint, but here we only want a non-enforced one.

Umm, wouldn't it make more sense to modify ATExecSetNotNull() so that it
does what we want?  This seems hackish.

> +			nnconstr->initially_valid = true;

Hmmm, this bit here (making constraints as valid if they're not valid in
the source table) looks like a fix for the existing code.  I think it
should be a separate patch, perhaps back-patchable to 18.  Or maybe I'm
missing something ...?

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Digital and video cameras have this adjustment and film cameras don't for the
same reason dogs and cats lick themselves: because they can."   (Ken Rockwell)

---

From: jian he <[email-masked]>
Date: Thu, 4 Sep 2025 23:20:42 +0800

> should be a separate patch, perhaps back-patchable to 18.  Or maybe I'm

it's indeed a bug, which was introduced
https://git.postgresql.org/cgit/postgresql.git/diff/src/backend/parser/parse_utilcmd.c?id=ca87c415e2fccf81cec6fd45698dde9fae0ab570

attached is the fix, also added a test on create_table_like.sql
From 6ee61e8b107a3fabe9cadb35701c7d9033889722 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Thu, 4 Sep 2025 23:16:49 +0800
Subject: [PATCH v1 1/1] fix CREATE TABLE LIKE with INVALID CHECK CONSTRAINT

bug was introduced
https://git.postgresql.org/cgit/postgresql.git/diff/src/backend/parser/parse_utilcmd.c?id=ca87c415e2fccf81cec6fd45698dde9fae0ab570

CREATE TABLE ... LIKE, any new check constraints copied from the source table
are expected to be valid if they are ENFORCED.

add a test on it.
---
 src/backend/parser/parse_utilcmd.c              | 3 +--
 src/test/regress/expected/create_table_like.out | 8 ++++++++
 src/test/regress/sql/create_table_like.sql      | 1 +
 3 files changed, 10 insertions(+), 2 deletions(-)

diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index afcf54169c3..f8673a82c49 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1461,7 +1461,6 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
 			char	   *ccname = constr->check[ccnum].ccname;
 			char	   *ccbin = constr->check[ccnum].ccbin;
 			bool		ccenforced = constr->check[ccnum].ccenforced;
-			bool		ccvalid = constr->check[ccnum].ccvalid;
 			bool		ccnoinherit = constr->check[ccnum].ccnoinherit;
 			Node	   *ccbin_node;
 			bool		found_whole_row;
@@ -1492,7 +1491,7 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
 			n->conname = pstrdup(ccname);
 			n->location = -1;
 			n->is_enforced = ccenforced;
-			n->initially_valid = ccvalid;
+			n->initially_valid = ccenforced ? true : false;
 			n->is_no_inherit = ccnoinherit;
 			n->raw_expr = NULL;
 			n->cooked_expr = nodeToString(ccbin_node);
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index 29a779c2e90..d3c35c14847 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -320,6 +320,7 @@ DROP TABLE inhz;
 -- including storage and comments
 CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) ENFORCED PRIMARY KEY,
 	b text CHECK (length(b) > 100) NOT ENFORCED);
+ALTER TABLE ctlt1 ADD CONSTRAINT cc CHECK (length(b) > 100) NOT VALID;
 CREATE INDEX ctlt1_b_key ON ctlt1 (b);
 CREATE INDEX ctlt1_fnidx ON ctlt1 ((a || b));
 CREATE STATISTICS ctlt1_a_b_stat ON a,b FROM ctlt1;
@@ -378,6 +379,7 @@ SELECT conname, description FROM pg_description, pg_constraint c WHERE classoid
 CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1);
 NOTICE:  merging column "a" with inherited definition
 NOTICE:  merging column "b" with inherited definition
+NOTICE:  merging constraint "cc" with inherited definition
 NOTICE:  merging constraint "ctlt1_a_check" with inherited definition
 NOTICE:  merging constraint "ctlt1_b_check" with inherited definition
 \d+ ctlt1_inh
@@ -387,6 +389,7 @@ NOTICE:  merging constraint "ctlt1_b_check" with inherited definition
  a      | text |           | not null |         | main     |              | A
  b      | text |           |          |         | extended |              | B
 Check constraints:
+    "cc" CHECK (length(b) > 100)
     "ctlt1_a_check" CHECK (length(a) > 2)
     "ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
 Not-null constraints:
@@ -409,6 +412,7 @@ NOTICE:  merging multiple inherited definitions of column "a"
  b      | text |           |          |         | extended |              | 
  c      | text |           |          |         | external |              | 
 Check constraints:
+    "cc" CHECK (length(b) > 100)
     "ctlt1_a_check" CHECK (length(a) > 2)
     "ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
     "ctlt3_a_check" CHECK (length(a) < 5)
@@ -430,6 +434,7 @@ NOTICE:  merging column "a" with inherited definition
 Indexes:
     "ctlt13_like_expr_idx" btree ((a || c))
 Check constraints:
+    "cc" CHECK (length(b) > 100)
     "ctlt1_a_check" CHECK (length(a) > 2)
     "ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
     "ctlt3_a_check" CHECK (length(a) < 5)
@@ -456,6 +461,7 @@ Indexes:
     "ctlt_all_b_idx" btree (b)
     "ctlt_all_expr_idx" btree ((a || b))
 Check constraints:
+    "cc" CHECK (length(b) > 100)
     "ctlt1_a_check" CHECK (length(a) > 2)
     "ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
 Statistics objects:
@@ -499,6 +505,7 @@ Indexes:
     "pg_attrdef_b_idx" btree (b)
     "pg_attrdef_expr_idx" btree ((a || b))
 Check constraints:
+    "cc" CHECK (length(b) > 100)
     "ctlt1_a_check" CHECK (length(a) > 2)
     "ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
 Statistics objects:
@@ -524,6 +531,7 @@ Indexes:
     "ctlt1_b_idx" btree (b)
     "ctlt1_expr_idx" btree ((a || b))
 Check constraints:
+    "cc" CHECK (length(b) > 100)
     "ctlt1_a_check" CHECK (length(a) > 2)
     "ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
 Statistics objects:
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index bf8702116a7..93389b57dbf 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -130,6 +130,7 @@ DROP TABLE inhz;
 -- including storage and comments
 CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) ENFORCED PRIMARY KEY,
 	b text CHECK (length(b) > 100) NOT ENFORCED);
+ALTER TABLE ctlt1 ADD CONSTRAINT cc CHECK (length(b) > 100) NOT VALID;
 CREATE INDEX ctlt1_b_key ON ctlt1 (b);
 CREATE INDEX ctlt1_fnidx ON ctlt1 ((a || b));
 CREATE STATISTICS ctlt1_a_b_stat ON a,b FROM ctlt1;
-- 
2.34.1

---

From: Álvaro Herrera <[email-masked]>
Date: Fri, 5 Sep 2025 21:23:30 +0200

> does what we want?  This seems hackish.

BTW while you're at that, it might make sense to allow commands like
  ALTER TABLE foo ALTER col1 SET NOT NULL NOT VALID
  ALTER TABLE foo ALTER col1 SET NOT NULL NOT ENFORCED

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"La verdad no siempre es bonita, pero el hambre de ella sí"

---

From: jian he <[email-masked]>
Date: Mon, 8 Sep 2025 18:39:14 +0800

> make sure that's what we want.

In CheckNNConstraintFetch, I changed it to
"""
        if (conform->contype == CONSTRAINT_NOTNULL)
        {
            if (!conform->convalidated && conform->conenforced)
            {
                AttrNumber    attnum;
                attnum = extractNotNullColumn(htup);
                Assert(relation->rd_att->compact_attrs[attnum -
1].attnullability ==
                       ATTNULLABLE_UNKNOWN);
                relation->rd_att->compact_attrs[attnum - 1].attnullability =
                    ATTNULLABLE_INVALID;
            }
            continue;
        }
"""

set pg_attribute.attnotnull to true for not-valid not-null is still
useful for INSERT/UPDATE.
set pg_attribute.attnotnull to true for not-enforced not-null
constraints doesn't have real benefits, IMHO.

If we let pg_attribute.attnotnull to true for not-enforced not-null,
then do we need to change the definition of
TupleConstr->has_not_null?

---

From: Álvaro Herrera <[email-masked]>
Date: Mon, 8 Sep 2025 14:01:42 +0200

> constraints doesn't have real benefits, IMHO.

Yeah, you might be right about this actually.  What we wanted attnotnull
to be set for, as I recall, was so that generic apps could alert the
user that an insert that tries to put a NULL value in that column would
not work, without having to actually execute it.  With a non-enforced

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"The important things in the world are problems with society that we don't
understand at all. The machines will become more complicated but they won't
be more complicated than the societies that run them."    (Freeman Dyson)

---

From: Peter Eisentraut <[email-masked]>
Date: Wed, 10 Sep 2025 13:51:04 +0200

> attached is the fix, also added a test on create_table_like.sql

I have committed this fix.

---

From: jian he <[email-masked]>
Date: Wed, 24 Sep 2025 18:30:33 +0800

> to get them combined resulting in one enforced constraint.

changed accordingly.
When a column can inherit multiple not-null constraints. If one is not enforced,
another one is enforced then we will install an enforced one.

> enforced, rather than drop it.

The hint also changed.

+ /*
+ * If the ENFORCED status we're asked for doesn't match what the
+ * existing constraint has, 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."));
+ }

> does what we want?  This seems hackish.

modified ATExecSetNotNull for ATExecAlterConstrInheritability usage.
now ATExecSetNotNull is

ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
                               bool recurse, bool recursing, bool
is_enforced, LOCKMODE lockmode)

new patch attached with the pg_dump TAP tests.
currently NOT VALID NOT NULL dumped
constraint separately, NOT NULL NOT ENFORCED constraints can also be dumped
separately.

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.
CREATE TABLE tx2 (x int check (x > 1) not enforced);
will be dumped as

CREATE TABLE public.tx2 (x integer);
ALTER TABLE public.tx2
    ADD CONSTRAINT tx2_x_check CHECK ((x > 1)) NOT ENFORCED;
From e34fd68c4acf925fb241ce8d842ef66b91ad4795 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Wed, 24 Sep 2025 14:00:11 +0800
Subject: [PATCH v2 1/1] NOT NULL NOT ENFORCED

reference:  https://git.postgresql.org/cgit/postgresql.git/commit/?id=a379061a22a8fdf421e1a457cc6af8503def6252
discussion: https://postgr.es/m/CACJufxFbH1_9BDow=[email-masked]
---
 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                    |  62 +++++--
 src/backend/catalog/pg_constraint.c           |  50 ++++--
 src/backend/catalog/sql_features.txt          |   2 +-
 src/backend/commands/tablecmds.c              | 121 ++++++++++---
 src/backend/parser/gram.y                     |   2 +-
 src/backend/parser/parse_utilcmd.c            |  80 ++++++---
 src/backend/utils/cache/relcache.c            |   4 +-
 src/bin/pg_dump/pg_dump.h                     |   1 +
 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           |   4 +-
 src/test/regress/expected/constraints.out     | 163 ++++++++++++++++++
 .../regress/expected/create_table_like.out    |  22 +++
 src/test/regress/expected/inherit.out         | 130 ++++++++++++++
 src/test/regress/sql/constraints.sql          |  90 ++++++++++
 src/test/regress/sql/create_table_like.sql    |  12 ++
 src/test/regress/sql/inherit.sql              |  70 ++++++++
 22 files changed, 782 insertions(+), 96 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index e9095bedf21..d1efb96fe0a 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1260,7 +1260,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>

    <para>
 
      <para>
index fd6537567ea..1eccbbb5f03 100644

 
 			/*
+			 * requested validity, enforceability.
 
 			/*
 
 			constrOid =
+		 * that the NO INHERIT, [NOT] ENFORCED flags match.

+				/*

 
 	/*
@@ -3093,6 +3115,17 @@ 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
@@ -3123,10 +3156,13 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
 		nnnames = lappend(nnnames, conname);

 
 	return nncols;
index 6002fd0002f..fe8a9ba7912 100644

 
 /*
 
 /*
  * 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).
  *
@@ -738,8 +739,8 @@ extractNotNullColumn(HeapTuple constrTup)
  * it's already true; otherwise we increment coninhcount by 1.
  */
 bool
-AdjustNotNullInheritance(Oid relid, AttrNumber attnum,
-						 bool is_local, bool is_no_inherit, bool is_notvalid)
+AdjustNotNullInheritance(Oid relid, AttrNumber attnum, bool is_local,
+						 bool is_no_inherit, bool is_notvalid, bool is_enforced)
 {
 	HeapTuple	tup;
 
@@ -777,6 +778,26 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum,
 
+		/*
+		 * If the ENFORCED status we're asked for doesn't match what the
+		 * existing constraint has, 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,
@@ -807,6 +828,7 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum,
  * 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.
@@ -853,7 +875,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;
@@ -873,7 +895,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 = true;
 			constr->is_no_inherit = conForm->connoinherit;
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index ebe85337c28..c418e6cd1dd 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 3be2e051d32..b1416d55b68 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,
@@ -2744,13 +2745,16 @@ MergeAttributes(List *columns, const List *supers, char relpersistence,
 		inherited_defaults = cols_with_defaults = NIL;
 
 		/*
 
 		for (AttrNumber parent_attno = 1; parent_attno <= tupleDesc->natts;
 			 parent_attno++)
@@ -5407,7 +5411,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);
@@ -7730,6 +7734,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,
@@ -7758,13 +7763,6 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
 	ObjectAddressSubSet(address, RelationRelationId,
 						RelationGetRelid(rel), attnum);
 
@@ -7803,8 +7801,17 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
 
 	/* The normal case: we have a pg_constraint row, remove it */
 	dropconstraint_internal(rel, conTup, DROP_RESTRICT, recurse, false,
@@ -7901,10 +7908,14 @@ 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.
+ *
+ * If is_enforced is false, the added NOT NULL constraint is 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;
@@ -7939,7 +7950,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))
 	{
@@ -7956,6 +7967,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
@@ -7975,7 +7993,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
@@ -8036,6 +8054,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),
@@ -8047,7 +8068,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.
@@ -8066,7 +8088,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);
 		}
 	}
@@ -9563,7 +9585,7 @@ verifyNotNullPKCompatible(HeapTuple tuple, const char *colname)
 						"ALTER TABLE ... ALTER CONSTRAINT ... INHERIT"));
 
@@ -9573,6 +9595,17 @@ verifyNotNullPKCompatible(HeapTuple tuple, const char *colname)
+				errhint("You might need to ensure the existing constraint is enforced."));
 }
 
 /*
@@ -9941,9 +9974,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. For not enforced not-null, no need to set attnotnull.
 		 */
-		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);
@@ -12641,7 +12674,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);
@@ -17509,9 +17544,28 @@ MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel, bool ispart
 			if (parent_att->attnotnull && !child_att->attnotnull)
 			{
 				HeapTuple	contup;
+				HeapTuple	childcontup;
 
+					 * merge with an enforced parent constraint
@@ -17763,13 +17817,24 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel)
 
 			ereport(ERROR,
index 9fd48acb1f8..ac779bb4d31 100644
@@ -4259,7 +4259,7 @@ ConstraintElem:
index e96b38a59d5..aad8f292ed9 100644

 

 
 
@@ -3991,7 +4021,8 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
@@ -4008,7 +4039,8 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
index 6fe268a8eec..1b1ba4d4e5f 100644
@@ -4623,7 +4623,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
@@ -4632,7 +4632,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/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index bcc94ff07cc..0fbe0a42e65 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -375,6 +375,7 @@ typedef struct _tableInfo
 	char	  **notnull_comment;	/* comment thereof */
 	bool	   *notnull_invalid;	/* true for NOT NULL NOT VALID */
 	bool	   *notnull_noinh;	/* NOT NULL is NO INHERIT */
+	bool	   *notnull_enforced; 	/* NOT NULL is ENFORCED */
 	bool	   *notnull_islocal;	/* true if NOT NULL has local definition */
 	struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
 	struct _constraintInfo *checkexprs; /* CHECK constraints */
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index fc5b9b52f80..375dd9b344f 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -1226,6 +1226,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 4aa793d7de7..543ed309b92 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3108,7 +3108,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"
@@ -3132,15 +3139,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';

 				printTableAddFooter(&cont, buf.data);
 			}
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h
index a25b94ba423..a810159d4a3 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 dbd339e9df4..5e7fe1d2f69 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 4afceb5c692..fc581701c61 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -263,8 +263,8 @@ extern HeapTuple findNotNullConstraintAttnum(Oid relid, AttrNumber attnum);
 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,
-									 bool is_local, bool is_no_inherit, bool is_notvalid);
+extern bool AdjustNotNullInheritance(Oid relid, AttrNumber attnum, bool is_local,
+									 bool is_no_inherit, bool is_notvalid, bool is_enforced);
 extern List *RelationGetNotNullConstraints(Oid relid, bool cooked,
 										   bool include_noinh);
 
index 3590d3274f0..fdae98c58f3 100644
@@ -961,33 +961,136 @@ drop table notnull_tbl2, notnull_tbl3, notnull_tbl4, notnull_tbl5, notnull_tbl6;
+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.
+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.
+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.
+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.
+HINT:  You might need to ensure the existing constraint is enforced.
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn NO INHERIT; --ok
+    "nn" NOT NULL "x" NO INHERIT NOT ENFORCED
@@ -1668,6 +1771,66 @@ COMMENT ON CONSTRAINT constr_parent2_a_not_null ON constr_parent2 IS 'this const
index d3c35c14847..36afb096fde 100644
 
 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 5b5055babdc..4c555efd55d 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1439,6 +1439,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
+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 have enforced not-null
+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_c1_nn_1 not null f1 enforced; --error, column f1 already have not-enforced not-null
+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 have 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 1f6dc8fd69f..27333168150 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -663,22 +663,69 @@ drop table notnull_tbl2, notnull_tbl3, notnull_tbl4, notnull_tbl5, notnull_tbl6;
 
 -- error cases:
 
 drop table notnull_tbl1;
 
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn NO INHERIT; --ok
@@ -1008,6 +1055,49 @@ DEALLOCATE get_nnconstraint_info;

index 93389b57dbf..59cf66edd5d 100644
 
+CREATE TABLE not_enforced_nn (a text, CONSTRAINT nn NOT NULL a NOT ENFORCED);
index 699e8ac09c8..63f3d65cf12 100644
@@ -527,6 +527,76 @@ create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
 
+--
+-- 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
+create table p1_c4(f1 int not null not enforced) inherits(p1, p1_c1); --error, parent have enforced not-null
+--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_c1_nn_1 not null f1 enforced; --error, column f1 already have not-enforced not-null
+
+-- not allowed: child is not enforced, parent have 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

---

From: Álvaro Herrera <[email-masked]>
Date: Thu, 25 Sep 2025 11:46:28 +0200

> ALTER TABLE public.tx3 ADD CONSTRAINT tx3_x_not_null NOT NULL x NOT ENFORCED;

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.