Re: NOT NULL NOT ENFORCED

On Thu, Sep 4, 2025 at 8:00 PM Álvaro Herrera <alvherre(at)kurilemu(dot)de> wrote:
>
> > @@ -1272,33 +1294,41 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
> > * Reproduce not-null constraints, if any, by copying them. We do this
> > * regardless of options given.
> > */
> > - if (tupleDesc->constr && tupleDesc->constr->has_not_null)
> > - {
> > - List *lst;
> > + lst = RelationGetNotNullConstraints(RelationGetRelid(relation), false,
> > + true);
> > + cxt->nnconstraints = list_concat(cxt->nnconstraints, lst);
> >
> > - lst = RelationGetNotNullConstraints(RelationGetRelid(relation), false,
> > - true);
>
> > + /*
> > + * When creating a new relation, marking the enforced not-null constraint as
> > + * not valid doesn't make sense, so we treat it as valid.
> > + */
> > + foreach_node(Constraint, nnconstr, lst)
> > + {
> > + if (nnconstr->is_enforced)
> > + {
> > + nnconstr->skip_validation = false;
> > + nnconstr->initially_valid = true;
> > + }
> > + }
>
> Hmmm, this bit here (making constraints as valid if they're not valid in
> the source table) looks like a fix for the existing code. I think it
> should be a separate patch, perhaps back-patchable to 18. Or maybe I'm
> missing something ...?
>

it's indeed a bug, which was introduced
https://git.postgresql.org/cgit/postgresql.git/diff/src/backend/parser/parse_utilcmd.c?id=ca87c415e2fccf81cec6fd45698dde9fae0ab570

attached is the fix, also added a test on create_table_like.sql