On 2025/06/18 9:13, Fujii Masao wrote:
> Hi,
>
> In v18, we can now add comments on NOT NULL constraints. However, I noticed
> that pg_dump doesn't include those comments in its output. For example:
>
> --------------------------
> $ psql <<EOF
> CREATE TABLE t (i int);
> ALTER TABLE t ADD CONSTRAINT my_not_null NOT NULL i;
> ALTER TABLE t ADD CONSTRAINT my_check CHECK (i > 0);
> COMMENT ON CONSTRAINT my_not_null ON t IS 'my not null';
> COMMENT ON CONSTRAINT my_check ON t IS 'my check';
> EOF
>
> $ pg_dump | grep COMMENT
> -- Name: CONSTRAINT my_check ON t; Type: COMMENT; Schema: public; Owner: postgres
> COMMENT ON CONSTRAINT my_check ON public.t IS 'my check';
> --------------------------
>
> As shown above, the comment on my_not_null is missing from the dump output.
>
> Is this an oversight in commit 14e87ffa5c5? If so, I'll add it as
> a v18 open item.
>
> I'm aware of a related open item [1] affecting both v17 and v18,
> but this seems like a separate issue, since it relates to a new v18 feature...
> Or we should treat them the same?
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);
=# SELECT cls.relname, cnst.conname, obj_description(cnst.oid, 'pg_constraint')
FROM pg_constraint cnst, pg_class cls
WHERE cnst.conrelid = cls.oid AND cnst.conname like '%my_%'
ORDER BY cls.relname, cnst.conname;
relname | conname | obj_description
----------+---------------+-------------------
t | my_check_i | my check for i
t | my_not_null_i | my not null for i
t_copied | my_check_i | my check for i
t_copied | my_not_null_i | (null)
(4 rows)
-----------------------------------------------------
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?
Regards,
--
Fujii Masao
NTT DATA Japan Corporation
