Re: ALTER COLUMN SET NOT NULL differ from ALTER TABLE ADD CONSTRAINT

Hello Jian,

Following up on the issue identified during the investigation of the
potential pg_dump output-related bug.

I wanted to highlight one additional concern regarding the behavior of ALTER
TABLE ... ALTER COLUMN ... SET NOT NULL when a descendant table has an
existing NOT VALID NOT NULL constraint.

In the inheritance example provided earlier, should this operation fail, or
should it ensure that the corresponding constraint on the child table is
also marked as valid and enforced?

I noticed that a similar issue appears to exist with partitioned tables as
well.

Could you please clarify whether this behavior is expected or whether it
should be addressed as part of the existing fix?

Thanks!

Osama Abdul Qader

On Mon, 19 Oct 2026 at 19:01, jian he <jian(dot)universality(at)gmail(dot)com> wrote:

> Hi.
>
> While trying to check
>
> https://git.postgresql.org/cgit/postgresql.git/commit/?id=96e2af605043974137d84edf5c0a24561956919e
> potential pg_dump output related bug.
>
> I encountered the following issue first.
>
> I expect ALTER TABLE ADD CONSTRAINT to have the same effect as ALTER
> COLUMN SET
> NOT NULL, meaning that the column associated constraint will be marked as
> VALID
> and ENFORCED across the entire table inheritance hierarchy.
>
> I think it is ok for one form to fail (error out) while the other succeeds.
> However, if ALTER COLUMN SET NOT NULL succeeds, all associated NOT NULL
> constraints should be marked as VALID and ENFORCED.
>
> drop table if exists tp, tc cascade;
> create table tp (a int);
> create table tc (a int) inherits (tp);
> alter table tc add constraint nn not null a not valid;
> alter table tp add constraint nn not null a; -- error
> alter table tp alter column a set not null; -- should expect an error
>
> The last ALTER COLUMN SET NOT NULL should either error out or make the
> column
> 'a' in table "tc" have a not-null constraint that is ENFORCED and VALID?
>
> The partitioned table has a similar issue.
>
>
>
> --
> jian
> https://www.enterprisedb.com/
>
>
>