On 2025/06/19 20:53, Fujii Masao wrote:
On 2025/06/19 14:42, jian he wrote:
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.
Thanks for the patch! LGTM.
Just one minor suggestion:
+ /* Copy comments on not-null constraints */
+ if (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS)
+ {
It might be clearer to move this block after the line:
cxt->nnconstraints = list_concat(cxt->nnconstraints, lst);
That would make the code a bit more readable.
I've applied this cosmetic change to the patch.
Barring objections, I'll commit the patch.
Regards,
--
Fujii Masao
NTT DATA Japan Corporation
From 516e647e7d1fdafc64dba092389963f32cd688e5 Mon Sep 17 00:00:00 2001
From: Fujii Masao <fu...@postgresql.org>
Date: Wed, 25 Jun 2025 10:02:56 +0900
Subject: [PATCH v2] Make CREATE TABLE LIKE copy comments on NOT NULL
constraints when requested.
Commit 14e87ffa5c5 introduced support for adding comments to NOT NULL
constraints. However, CREATE TABLE LIKE INCLUDING COMMENTS did not copy
these comments to the new table. This was an oversight in that commit.
This commit corrects the behavior by ensuring CREATE TABLE LIKE to also copy
the comments on NOT NULL constraints when INCLUDING COMMENTS is specified.
Author: Jian He <jian.universal...@gmail.com>
Reviewed-by: Fujii Masao <masao.fu...@gmail.com>
Discussion:
https://postgr.es/m/127debef-e558-4784-9e24-0d5eaf91e...@oss.nttdata.com
---
src/backend/parser/parse_utilcmd.c | 22 +++++++++++++++++++
.../regress/expected/create_table_like.out | 15 ++++++++++++-
src/test/regress/sql/create_table_like.sql | 12 +++++++++-
3 files changed, 47 insertions(+), 2 deletions(-)
diff --git a/src/backend/parser/parse_utilcmd.c
b/src/backend/parser/parse_utilcmd.c
index 62015431fdf..afcf54169c3 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1279,6 +1279,28 @@ transformTableLikeClause(CreateStmtContext *cxt,
TableLikeClause *table_like_cla
lst = RelationGetNotNullConstraints(RelationGetRelid(relation),
false,
true);
cxt->nnconstraints = list_concat(cxt->nnconstraints, lst);
+
+ /* 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);
+ }
+ }
+ }
}
/*
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.49.0
