On Wed, Jun 18, 2025 at 10:21 AM Fujii Masao <masao.fu...@oss.nttdata.com> wrote: > > I ran into another issue related to comments on NOT NULL constraints. > When using CREATE TABLE ... (LIKE ... INCLUDING ALL), the NOT NULL constraints > are copied, but their comments are not. For example: > > ----------------------------------------------------- > =# CREATE TABLE t (i int); > =# ALTER TABLE t ADD CONSTRAINT my_not_null_i NOT NULL i; > =# ALTER TABLE t ADD CONSTRAINT my_check_i CHECK (i > 0); > =# COMMENT ON CONSTRAINT my_not_null_i ON t IS 'my not null for i'; > =# COMMENT ON CONSTRAINT my_check_i ON t IS 'my check for i'; > > =# CREATE TABLE t_copied (LIKE t INCLUDING ALL); > > As shown, the comment on my_not_null_i is not copied to the new table, > even though the constraint itself is. Could this be another oversight > in commit 14e87ffa5c5? >
hi. in transformTableLikeClausem, let cxt(CreateStmtContext) to add CommentStmt should just work. Please check attached, tests also added.
From 41fd109bd75451cadc347cc37be56228e85f3417 Mon Sep 17 00:00:00 2001 From: jian he <jian.universality@gmail.com> Date: Thu, 19 Jun 2025 13:40:06 +0800 Subject: [PATCH v1 1/1] fix create table like not copy not null constraint's comments We copy the source table's not-null constraints unconditionally. when using CREATE TABLE ... LIKE (INCLUDING COMMENT), the comments on the source table's not-null constraints should also be copied to the new destination table. this is a oversight of commit 14e87ffa5c5([1]) reported by: Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> [1] https://git.postgresql.org/cgit/postgresql.git/commit/?id=14e87ffa5c543b5f30ead7413084c25f7735039f discussion: 127debef-e558-4784-9e24-0d5eaf91e2d1@oss.nttdata.com">https://postgr.es/m/127debef-e558-4784-9e24-0d5eaf91e2d1@oss.nttdata.com --- src/backend/parser/parse_utilcmd.c | 23 +++++++++++++++++++ .../regress/expected/create_table_like.out | 15 +++++++++++- src/test/regress/sql/create_table_like.sql | 12 +++++++++- 3 files changed, 48 insertions(+), 2 deletions(-) diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 62015431fdf..2bd18c597d7 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -1278,6 +1278,29 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla lst = RelationGetNotNullConstraints(RelationGetRelid(relation), false, true); + + /* Copy comments on not-null constraints */ + if (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS) + { + foreach_node(Constraint, nnconstr, lst) + { + if ((comment = GetComment(get_relation_constraint_oid(RelationGetRelid(relation), + nnconstr->conname, false), + ConstraintRelationId, + 0)) != NULL) + { + 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); + } + } + } + cxt->nnconstraints = list_concat(cxt->nnconstraints, lst); } diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index bf34289e984..1374a972e6e 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -529,7 +529,9 @@ NOTICE: drop cascades to table inhe -- LIKE must respect NO INHERIT property of constraints CREATE TABLE noinh_con_copy (a int CHECK (a > 0) NO INHERIT, b int not null, c int not null no inherit); -CREATE TABLE noinh_con_copy1 (LIKE noinh_con_copy INCLUDING CONSTRAINTS); +COMMENT ON CONSTRAINT noinh_con_copy_b_not_null ON noinh_con_copy IS 'not null b'; +COMMENT ON CONSTRAINT noinh_con_copy_c_not_null ON noinh_con_copy IS 'not null c no inherit'; +CREATE TABLE noinh_con_copy1 (LIKE noinh_con_copy INCLUDING CONSTRAINTS INCLUDING COMMENTS); \d+ noinh_con_copy1 Table "public.noinh_con_copy1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description @@ -543,6 +545,17 @@ Not-null constraints: "noinh_con_copy_b_not_null" NOT NULL "b" "noinh_con_copy_c_not_null" NOT NULL "c" NO INHERIT +SELECT conname, description +FROM pg_description, pg_constraint c +WHERE classoid = 'pg_constraint'::regclass +AND objoid = c.oid AND c.conrelid = 'noinh_con_copy1'::regclass +ORDER BY conname COLLATE "C"; + conname | description +---------------------------+----------------------- + noinh_con_copy_b_not_null | not null b + noinh_con_copy_c_not_null | not null c no inherit +(2 rows) + -- 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); diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql index 6e21722aaeb..6da7f4f0557 100644 --- a/src/test/regress/sql/create_table_like.sql +++ b/src/test/regress/sql/create_table_like.sql @@ -197,9 +197,19 @@ DROP TABLE ctlt1, ctlt2, ctlt3, ctlt4, ctlt12_storage, ctlt12_comments, ctlt1_in -- LIKE must respect NO INHERIT property of constraints CREATE TABLE noinh_con_copy (a int CHECK (a > 0) NO INHERIT, b int not null, c int not null no inherit); -CREATE TABLE noinh_con_copy1 (LIKE noinh_con_copy INCLUDING CONSTRAINTS); + +COMMENT ON CONSTRAINT noinh_con_copy_b_not_null ON noinh_con_copy IS 'not null b'; +COMMENT ON CONSTRAINT noinh_con_copy_c_not_null ON noinh_con_copy IS 'not null c no inherit'; + +CREATE TABLE noinh_con_copy1 (LIKE noinh_con_copy INCLUDING CONSTRAINTS INCLUDING COMMENTS); \d+ noinh_con_copy1 +SELECT conname, description +FROM pg_description, pg_constraint c +WHERE classoid = 'pg_constraint'::regclass +AND objoid = c.oid AND c.conrelid = 'noinh_con_copy1'::regclass +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); -- 2.34.1
