Thread: pg_dump misses comments on NOT NULL constraints

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?

Regards,

[1] https://www.postgresql.org/message-id/CACJufxF-0bqVR%3Dj4jonS6N2Ka6hHUpFyu3_3TWKNhOW_4yFSSg%40mail.gmail.com

-- 
Fujii Masao
NTT DATA Japan Corporation

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

On 2025-Jun-18, jian he wrote:

> Similarly we don't need to worry about not-null constraints that are
> dumped separately.
> dumpConstraint, dumpTableConstraintComment will do the job.

Right.

> dumpTableSchema handles dumping of table column definitions, and tells us which
> column print_notnull is true.  Since we already know which columns have had
> their not-null constraints printed, it makes sense to dump inline not-null
> comments here too.

I agree that this is roughly the right approach, but I think you're
doing it harder than it needs to be -- it might be easier to add a JOIN
to pg_description to the big query in getTableAttrs(), and add a pointer
to the returned string in tbinfo->notnull_comments[j] (for versions
earlier than 18, don't add the join and have it return constant NULL).
Then in dumpTableSchema, in the place where you added the new query,
just scan that array and print COMMENT ON commands for each valid
constraint where that's not a null pointer.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Aprender sin pensar es inútil; pensar sin aprender, peligroso" (Confucio)