Hi, It was reported in [1] that creating a partitioned table by using 'CREATE TABLE .. (LIKE ...)' can lead to a not null constraint with 'NO INHERIT' being copied. This is disallowed for partitioned tables but the LIKE option has accidentally allowed it.
This seems like an unlikely scenario in practice but since it is simple to fix, I think we should handle it. I'm attaching a patch which fixes this by explicitly checking for a not-null NO INHERIT constraint in transformTableLikeClause and throwing an error. Please take a look and let me know what you think. [1] https://www.postgresql.org/message-id/flat/19770-7c6e75fc30df7e06%40postgresql.org Thanks & Regards, Nitin Motiani Google
From 8d78ee7d5a8a5737492cbe539afbb61c61c92cd3 Mon Sep 17 00:00:00 2001 From: Nitin Motiani <[email protected]> Date: Wed, 21 Oct 2026 08:00:54 +0000 Subject: [PATCH v1] Reject NO INHERIT not-null constraints copied by LIKE to partitioned tables. Partitioned tables cannot have NO INHERIT not-null constraints, and specifying one directly in CREATE TABLE or via ALTER CONSTRAINT is rejected. However, CREATE TABLE ... (LIKE ...) PARTITION BY copies the source table's not-null constraints unconditionally, including their NO INHERIT flag, without that check. The resulting partitioned table claims the column is NOT NULL, but its partitions don't get the constraint, so NULLs can be inserted through the parent while the planner treats "col IS NULL" on the parent as constant false, giving wrong query results. Fix by raising the same error in transformTableLikeClause when a copied not-null constraint is NO INHERIT and the new table is partitioned. The existing regression test for this case did not catch the problem. Its source table had both a NO INHERIT not-null constraint and a NO INHERIT CHECK constraint, and was copied with INCLUDING ALL. The CHECK constraint raised its own error ("cannot add NO INHERIT constraint to partitioned table"), so the test failed as expected even though the not-null constraint had been accepted. Plain LIKE copies not-null constraints but not CHECK constraints, so in that case nothing was rejected. Add test cases that exercise the not-null and CHECK paths separately, and verify that inheritable not-null constraints are still copied and propagated to partitions. Tables already created in the inconsistent state are not repaired by this fix. Reported-by: Ann C <[email protected]> Discussion: https://postgr.es/m/[email protected] Backpatch-through: 18 --- src/backend/parser/parse_utilcmd.c | 21 ++++++++++ .../regress/expected/create_table_like.out | 38 ++++++++++++++++++- src/test/regress/sql/create_table_like.sql | 22 +++++++++++ 3 files changed, 80 insertions(+), 1 deletion(-) diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index f0c7755b8..361c9fa97 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -1286,6 +1286,27 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla lst = RelationGetNotNullConstraints(RelationGetRelid(relation), false, true); + + /* + * Partitioned tables cannot have NO INHERIT not-null constraints, so + * reject copying one, same as if it had been specified directly. + */ + if (cxt->ispartitioned) + { + foreach_node(Constraint, nnconstr, lst) + { + if (nnconstr->is_no_inherit) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("not-null constraints on partitioned tables cannot be NO INHERIT"), + errdetail("Not-null constraint \"%s\" on table \"%s\" is NO INHERIT.", + nnconstr->conname, + RelationGetRelationName(relation)), + parser_errposition(cxt->pstate, + table_like_clause->relation->location)); + } + } + cxt->nnconstraints = list_concat(cxt->nnconstraints, lst); /* Copy comments on not-null constraints */ diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index a23735b5f..18a6582bc 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -580,8 +580,44 @@ ORDER BY conname COLLATE "C"; -- fail, as partitioned tables don't allow NO INHERIT constraints CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL) PARTITION BY LIST (a); -ERROR: cannot add NO INHERIT constraint to partitioned table "noinh_con_copy1_parted" +ERROR: not-null constraints on partitioned tables cannot be NO INHERIT +LINE 1: CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INC... + ^ +DETAIL: Not-null constraint "noinh_con_copy_c_not_null" on table "noinh_con_copy" is NO INHERIT. DROP TABLE noinh_con_copy, noinh_con_copy1; +-- likewise for a NO INHERIT check constraint alone +CREATE TABLE noinh_chk_copy (a int CHECK (a > 0) NO INHERIT); +CREATE TABLE noinh_chk_copy_parted (LIKE noinh_chk_copy INCLUDING CONSTRAINTS) + PARTITION BY LIST (a); -- fail +ERROR: cannot add NO INHERIT constraint to partitioned table "noinh_chk_copy_parted" +DROP TABLE noinh_chk_copy; +-- Not-null constraints are copied even without INCLUDING CONSTRAINTS, so a +-- NO INHERIT one must be rejected for a partitioned table too. +CREATE TABLE noinh_nn_copy (a int, CONSTRAINT noinh_nn_copy_a_nn NOT NULL a NO INHERIT); +CREATE TABLE noinh_nn_copy_parted (LIKE noinh_nn_copy) PARTITION BY LIST (a); -- fail +ERROR: not-null constraints on partitioned tables cannot be NO INHERIT +LINE 1: CREATE TABLE noinh_nn_copy_parted (LIKE noinh_nn_copy) PARTI... + ^ +DETAIL: Not-null constraint "noinh_nn_copy_a_nn" on table "noinh_nn_copy" is NO INHERIT. +-- inheritable not-null constraints are still copied and reach the partitions +CREATE TABLE inh_nn_copy (a int, CONSTRAINT inh_nn_copy_a_nn NOT NULL a); +CREATE TABLE inh_nn_copy_parted (LIKE inh_nn_copy) PARTITION BY LIST (a); +CREATE TABLE inh_nn_copy_parted_def PARTITION OF inh_nn_copy_parted DEFAULT; +SELECT conrelid::regclass, conname, connoinherit, conislocal, coninhcount +FROM pg_constraint +WHERE contype = 'n' + AND conrelid IN ('inh_nn_copy_parted'::regclass, 'inh_nn_copy_parted_def'::regclass) +ORDER BY conrelid::regclass::text COLLATE "C"; + conrelid | conname | connoinherit | conislocal | coninhcount +------------------------+------------------+--------------+------------+------------- + inh_nn_copy_parted | inh_nn_copy_a_nn | f | t | 0 + inh_nn_copy_parted_def | inh_nn_copy_a_nn | f | f | 1 +(2 rows) + +INSERT INTO inh_nn_copy_parted VALUES (NULL); -- fail +ERROR: null value in column "a" of relation "inh_nn_copy_parted_def" violates not-null constraint +DETAIL: Failing row contains (null). +DROP TABLE noinh_nn_copy, inh_nn_copy, inh_nn_copy_parted; /* LIKE with other relation kinds */ CREATE TABLE ctlt4 (a int, b text); CREATE SEQUENCE ctlseq1; diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql index d52a93ef1..8c072fb28 100644 --- a/src/test/regress/sql/create_table_like.sql +++ b/src/test/regress/sql/create_table_like.sql @@ -219,6 +219,28 @@ CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL) DROP TABLE noinh_con_copy, noinh_con_copy1; +-- likewise for a NO INHERIT check constraint alone +CREATE TABLE noinh_chk_copy (a int CHECK (a > 0) NO INHERIT); +CREATE TABLE noinh_chk_copy_parted (LIKE noinh_chk_copy INCLUDING CONSTRAINTS) + PARTITION BY LIST (a); -- fail +DROP TABLE noinh_chk_copy; + +-- Not-null constraints are copied even without INCLUDING CONSTRAINTS, so a +-- NO INHERIT one must be rejected for a partitioned table too. +CREATE TABLE noinh_nn_copy (a int, CONSTRAINT noinh_nn_copy_a_nn NOT NULL a NO INHERIT); +CREATE TABLE noinh_nn_copy_parted (LIKE noinh_nn_copy) PARTITION BY LIST (a); -- fail +-- inheritable not-null constraints are still copied and reach the partitions +CREATE TABLE inh_nn_copy (a int, CONSTRAINT inh_nn_copy_a_nn NOT NULL a); +CREATE TABLE inh_nn_copy_parted (LIKE inh_nn_copy) PARTITION BY LIST (a); +CREATE TABLE inh_nn_copy_parted_def PARTITION OF inh_nn_copy_parted DEFAULT; +SELECT conrelid::regclass, conname, connoinherit, conislocal, coninhcount +FROM pg_constraint +WHERE contype = 'n' + AND conrelid IN ('inh_nn_copy_parted'::regclass, 'inh_nn_copy_parted_def'::regclass) +ORDER BY conrelid::regclass::text COLLATE "C"; +INSERT INTO inh_nn_copy_parted VALUES (NULL); -- fail +DROP TABLE noinh_nn_copy, inh_nn_copy, inh_nn_copy_parted; + /* LIKE with other relation kinds */ --
