NOT NULL NOT ENFORCED

From 6ee61e8b107a3fabe9cadb35701c7d9033889722 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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 e34fd68c4acf925fb241ce8d842ef66b91ad4795 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Wed, 24 Sep 2025 14:00:11 +0800
Subject: [PATCH v2 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/CACJufxFbH1_9BDow=4nMSdBfLSOAkiGD5hxO6bouWjZAyHbV+A@mail.gmail.com
---
 doc/src/sgml/catalogs.sgml                    |   2 +-
 doc/src/sgml/ref/alter_table.sgml             |   4 +-
 doc/src/sgml/ref/create_table.sgml            |   4 +-
 src/backend/catalog/heap.c                    |  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>
 
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..1eccbbb5f03 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, 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,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);
 
 		/* 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..fe8a9ba7912 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -100,9 +100,10 @@ CreateConstraintEntry(const char *constraintName,
 	ObjectAddresses *addrs_auto;
 	ObjectAddresses *addrs_normal;
 
-	/* Only CHECK or FOREIGN KEY constraint can be not enforced */
-	Assert(isEnforced || constraintType == CONSTRAINT_CHECK ||
-		   constraintType == CONSTRAINT_FOREIGN);
+	/* Only CHECK, FOREIGN KEY, NOT NULL constraint can be not enforced */
+	Assert(isEnforced || (constraintType == CONSTRAINT_CHECK ||
+		   constraintType == CONSTRAINT_FOREIGN ||
+		   constraintType == CONSTRAINT_NOTNULL));
 	/* NOT ENFORCED constraint must be NOT VALID */
 	Assert(isEnforced || !isValidated);
 
@@ -580,8 +581,8 @@ ChooseConstraintName(const char *name1, const char *name2,
 }
 
 /*
- * Find and return a copy of the pg_constraint tuple that implements a
- * (possibly not valid) not-null constraint for the given column of the
+ * Find and return a copy of the pg_constraint tuple that implements a (possibly
+ * not valid or not enforced) not-null constraint for the given column of the
  * given relation.  If no such constraint exists, return NULL.
  *
  * XXX This would be easier if we had pg_attribute.notnullconstr with the OID
@@ -634,8 +635,8 @@ findNotNullConstraintAttnum(Oid relid, AttrNumber attnum)
 
 /*
  * Find and return a copy of the pg_constraint tuple that implements a
- * (possibly not valid) not-null constraint for the given column of the
- * given relation.
+ * (possibly not valid or not enforced) not-null constraint for the given column
+ * of the given relation.
  * If no such column or no such constraint exists, return NULL.
  */
 HeapTuple
@@ -728,8 +729,8 @@ extractNotNullColumn(HeapTuple constrTup)
  * If no not-null constraint is found for the column, return false.
  * Caller can create one.
  *
- * If a constraint exists but the connoinherit flag is not what the caller
- * wants, throw an error about the incompatibility.  If the desired
+ * If a constraint exists but the connoinherit, conenforced flag is not what the
+ * caller wants, throw an error about the incompatibility.  If the desired
  * constraint is valid but the existing constraint is not valid, also
  * throw an error about that (the opposite case is acceptable).
  *
@@ -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,26 @@ 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)
+		{
+			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;
 
 		/*
-		 * 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++)
@@ -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);
 
-	/* 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,
@@ -7803,8 +7801,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,
@@ -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"));
 
 	/* 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),
@@ -9573,6 +9595,17 @@ verifyNotNullPKCompatible(HeapTuple tuple, const char *colname)
 						  get_rel_name(conForm->conrelid), "NOT VALID"),
 				errhint("You might need to validate it using %s.",
 						"ALTER TABLE ... VALIDATE CONSTRAINT"));
+
+	/* a not enforced constraint is no good */
+	if (!conForm->conenforced)
+		ereport(ERROR,
+				errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+				errmsg("cannot create primary key on column \"%s\"", colname),
+		/*- translator: fourth %s is a constraint characteristic such as NOT ENFORCED */
+				errdetail("The constraint \"%s\" on column \"%s\" of table \"%s\", marked %s, is incompatible with a primary key.",
+						  NameStr(conForm->conname), colname,
+						  get_rel_name(conForm->conrelid), "NOT ENFORCED"),
+				errhint("You might need to ensure the existing constraint is enforced."));
 }
 
 /*
@@ -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;
 
+				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 an 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,
@@ -17763,13 +17817,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 9fd48acb1f8..ac779bb4d31 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4259,7 +4259,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 e96b38a59d5..aad8f292ed9 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);
 			}
 		}
 	}
@@ -3991,7 +4021,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"),
@@ -4008,7 +4039,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..1b1ba4d4e5f 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -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';
 
-				printfPQExpBuffer(&buf, "    \"%s\" NOT NULL \"%s\"%s%s",
+				printfPQExpBuffer(&buf, "    \"%s\" NOT NULL \"%s\"%s",
 								  PQgetvalue(result, i, 0),
 								  PQgetvalue(result, i, 1),
 								  PQgetvalue(result, i, 2)[0] == 't' ?
 								  " NO INHERIT" :
 								  islocal && inherited ? _(" (local, inherited)") :
-								  inherited ? _(" (inherited)") : "",
-								  !validated ? " NOT VALID" : "");
+								  inherited ? _(" (inherited)") : "");
+
+				if (!enforced)
+					appendPQExpBufferStr(&buf, " NOT ENFORCED");
+				else if (!validated)
+					appendPQExpBufferStr(&buf, " NOT VALID");
 
 				printTableAddFooter(&cont, buf.data);
 			}
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h
index 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);
 
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 3590d3274f0..fdae98c58f3 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -961,33 +961,136 @@ 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 NOT NULL constraint "notnull_tbl1_c_not_null" on relation "notnull_tbl1" to not enforced
+HINT:  You might need to ensure the existing constraint is not enforced.
 drop table notnull_tbl1;
+-- NOT NULL [NOT] ENFORCED
+CREATE TABLE ne_nn_tbl (x int, CONSTRAINT nn NOT NULL x NOT ENFORCED, NOT NULL x ENFORCED); --error
+ERROR:  conflicting NOT ENFORCED declaration for not-null constraint on column "x"
+CREATE TABLE ne_nn_tbl (x int, CONSTRAINT nn NOT NULL x NOT ENFORCED);
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'nn';
+  pg_get_constraintdef   
+-------------------------
+ NOT NULL x NOT ENFORCED
+(1 row)
+
+INSERT INTO ne_nn_tbl VALUES (NULL); --ok
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn ENFORCED; --error
+ERROR:  cannot alter enforceability of constraint "nn" of relation "ne_nn_tbl"
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn NOT ENFORCED; --error
+ERROR:  cannot alter enforceability of constraint "nn" of relation "ne_nn_tbl"
+ALTER TABLE ne_nn_tbl VALIDATE CONSTRAINT nn; --error
+ERROR:  cannot validate NOT ENFORCED constraint
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT ENFORCED 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 not enforced NOT NULL constraint "nn" on relation "ne_nn_tbl" to enforced
+HINT:  You might need to ensure the existing constraint is enforced.
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced NOT NULL x ENFORCED; --error
+ERROR:  cannot change not enforced NOT NULL constraint "nn" on relation "ne_nn_tbl" to enforced
+HINT:  You might need to ensure the existing constraint is enforced.
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced NOT NULL x; --error, one column can only one not-null
+ERROR:  cannot change not enforced NOT NULL constraint "nn" on relation "ne_nn_tbl" to enforced
+HINT:  You might need to ensure the existing constraint is enforced.
+ALTER TABLE ne_nn_tbl ALTER COLUMN x SET NOT NULL; --error
+ERROR:  cannot validate NOT ENFORCED constraint "nn" on relation "ne_nn_tbl"
+\d+ ne_nn_tbl
+                                 Table "public.ne_nn_tbl"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ x      | integer |           |          |         | plain   |              | 
+Not-null constraints:
+    "nn" NOT NULL "x" NOT ENFORCED
+
+TRUNCATE ne_nn_tbl;
+--error, 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 ensure the existing constraint is enforced.
+ALTER TABLE ne_nn_tbl ADD column y int NOT NULL NOT ENFORCED ENFORCED; --error
+ERROR:  multiple ENFORCED/NOT ENFORCED clauses not allowed
+LINE 1: ...E ne_nn_tbl ADD column y int NOT NULL NOT ENFORCED ENFORCED;
+                                                              ^
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn NO INHERIT; --ok
+ALTER TABLE ne_nn_tbl ADD column x1 int NOT NULL NOT ENFORCED, ADD column y int NOT NULL ENFORCED; --ok
+\d+ ne_nn_tbl
+                                 Table "public.ne_nn_tbl"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ x      | integer |           |          |         | plain   |              | 
+ x1     | integer |           |          |         | plain   |              | 
+ y      | integer |           | not null |         | plain   |              | 
+Not-null constraints:
+    "nn" NOT NULL "x" NO INHERIT NOT ENFORCED
+    "ne_nn_tbl_x1_not_null" NOT NULL "x1" NOT ENFORCED
+    "ne_nn_tbl_y_not_null" NOT NULL "y"
+
 -- NOT NULL NO INHERIT
 CREATE TABLE ATACC1 (a int, NOT NULL a NO INHERIT);
 CREATE TABLE ATACC2 () INHERITS (ATACC1);
@@ -1668,6 +1771,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 d3c35c14847..36afb096fde 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -317,6 +317,28 @@ Referenced by:
     TABLE "inhz" CONSTRAINT "inhz_x_fkey" FOREIGN KEY (x) REFERENCES inhz(xx)
 
 DROP TABLE inhz;
+--not null not enforced constraint
+CREATE TABLE not_enforced_nn (a text, CONSTRAINT nn NOT NULL a NOT ENFORCED);
+COMMENT ON CONSTRAINT nn ON not_enforced_nn is 'not enforced not null constraint comment test';
+CREATE TABLE not_enforced_nn_copy(LIKE not_enforced_nn INCLUDING CONSTRAINTS INCLUDING COMMENTS);
+\d+ not_enforced_nn_copy
+                          Table "public.not_enforced_nn_copy"
+ Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+------+-----------+----------+---------+----------+--------------+-------------
+ a      | text |           |          |         | extended |              | 
+Not-null constraints:
+    "nn" NOT NULL "a" NOT ENFORCED
+
+SELECT conname, description
+FROM  pg_description, pg_constraint c
+WHERE classoid = 'pg_constraint'::regclass
+AND   objoid = c.oid AND c.conrelid = 'not_enforced_nn_copy'::regclass
+ORDER BY conname COLLATE "C";
+ conname |                  description                  
+---------+-----------------------------------------------
+ nn      | not enforced not null constraint comment test
+(1 row)
+
 -- including storage and comments
 CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) ENFORCED PRIMARY KEY,
 	b text CHECK (length(b) > 100) NOT ENFORCED);
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 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:
 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);
+
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'nn';
+INSERT INTO ne_nn_tbl VALUES (NULL); --ok
+
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn ENFORCED; --error
+ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn NOT ENFORCED; --error
+ALTER TABLE ne_nn_tbl VALIDATE CONSTRAINT nn; --error
+
+ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT ENFORCED 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 ALTER CONSTRAINT nn NO INHERIT; --ok
+ALTER TABLE ne_nn_tbl ADD column x1 int NOT NULL NOT ENFORCED, ADD column y int NOT NULL ENFORCED; --ok
+\d+ ne_nn_tbl
+
 -- NOT NULL NO INHERIT
 CREATE TABLE ATACC1 (a int, NOT NULL a NO INHERIT);
 CREATE TABLE ATACC2 () INHERITS (ATACC1);
@@ -1008,6 +1055,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 93389b57dbf..59cf66edd5d 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -127,6 +127,18 @@ CREATE TABLE inhz (x text REFERENCES inhz, LIKE inhx INCLUDING INDEXES);
 \d inhz
 DROP TABLE inhz;
 
+--not null not enforced constraint
+CREATE TABLE not_enforced_nn (a text, CONSTRAINT nn NOT NULL a NOT ENFORCED);
+COMMENT ON CONSTRAINT nn ON not_enforced_nn is 'not enforced not null constraint comment test';
+CREATE TABLE not_enforced_nn_copy(LIKE not_enforced_nn INCLUDING CONSTRAINTS INCLUDING COMMENTS);
+\d+ not_enforced_nn_copy
+
+SELECT conname, description
+FROM  pg_description, pg_constraint c
+WHERE classoid = 'pg_constraint'::regclass
+AND   objoid = c.oid AND c.conrelid = 'not_enforced_nn_copy'::regclass
+ORDER BY conname COLLATE "C";
+
 -- including storage and comments
 CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) ENFORCED PRIMARY KEY,
 	b text CHECK (length(b) > 100) NOT ENFORCED);
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 699e8ac09c8..63f3d65cf12 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -527,6 +527,76 @@ create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
 alter table p1_c1 inherit p1;
 drop table p1, p1_c1;
 
+--
+-- Similarly, check the merging of existing constraints; a parent not-null constraint
+-- marked as NOT ENFORCED can merge with an ENFORCED child constraint, but the
+-- reverse is not allowed.
+--
+create table p1(f1 int constraint p1_a_nn not null);
+create table p1_c1(f1 int constraint p1_c1_nn not null not enforced);
+alter table p1_c1 inherit p1; --error
+create table p1_c2(f1 int not null not enforced) inherits(p1); --error
+create table p1_c2(f1 int not null not enforced) inherits(p1_c1); --ok
+select conenforced from pg_constraint where conrelid::regclass::text = ANY ('{p1_c2}') and contype = 'n';
+drop table if exists p1, p1_c1, p1_c2;
+
+
+create table p1(f1 int constraint p1_a_nn not null not enforced);
+create table p1_c1(f1 int constraint p1_c1_a_nn not null);
+alter table p1_c1 inherit p1; --it's ok for parent not-null is not enforced while child is enforced
+create table p1_c2() inherits(p1, p1_c1); --merged multiple not-null constraints produce an enforced one
+\d+ p1_c2
+create table p1_c3(f1 int);
+alter table p1_c3 inherit p1; --error
+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