Re: NOT NULL NOT ENFORCED

On Thu, Sep 4, 2025 at 8:00 PM Álvaro Herrera <alvherre@kurilemu.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