Re: [PATCH v1] Fix for bug #19770 : Reject NO INHERIT not-null constraints copied by LIKE to partitioned tables

> On Oct 22, 2026, at 10:55, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Wed, 21 Oct 2026 at 21:32, Nitin Motiani <nitinmotiani(at)google(dot)com> wrote:
>> I'm attaching a patch which fixes this by explicitly checking for a
>> not-null NO INHERIT constraint in transformTableLikeClause and
>> throwing an error.
>
> Looking at the test added in cacef1722, we do seem to expect that a NO
> INHERIT CHECK constraint will block the creation of a partitioned
> table with CREATE TABLE LIKE, so you could argue that it makes sense
> to do the same for NOT NULL constraints. If we were to go down that
> route, then I think the correct place to put the check would be in
> StoreRelNotNull() as the neighbouring StoreConstraints() is where
> cacef1722's test hits the error for the CHECK constraint. Putting it
> there also helps ensure there's no other place that could bypass this
> check.
>
> Having said that, I am wondering if this might just be annoying to
> users. If the user wants to bypass copying the CHECK constraints, they
> can use CREATE TABLE ... (LIKE ... EXCLUDING CONSTRAINTS), then maybe
> they have to manually adjust and add the CHECK constraints into the
> new table, but, going by [1]:
>
> "The LIKE clause specifies a table from which the new table
> automatically copies all column names, their data types, and their
> not-null constraints."
>
> and the final sentence of:
>
> "CHECK constraints will be copied. No distinction is made between
> column constraints and table constraints. Not-null constraints are
> always copied to the new table."
>
> So, it looks like there's no way to skip including the NOT NULL
> constraints, meaning the only way to avoid the error would be to
> modify the source table. If they're not willing to do that, then
> CREATE TABLE LIKE is no good for them.
>
> I've included a patch which puts a new error check in
> StoreRelNotNull() and spent some time thinking about the best way to
> resolve databases with wrongly set connoinherit flags for partitioned
> tables. If those are not fixed then dump/restore will fail as the
> CREATE TABLE will include a " NO INHERIT" for the NOT NULL
> constraint(s) for the partitioned table. I added some advice in the
> commit message which basically gives a query to find these, then goes
> on to mention using DROP NOT NULL to get rid of them and ALTER
> CONSTRAINT ... INHERIT to fix them.
>
> We could also entertain the idea of forcing these to connoinherit =
> false or ignoring no inherit constraints
> (RelationGetNotNullConstraints() does have a flag for that), but that
> could always be done at a later date when there is more time to
> consider any unwanted side-effects of that.
>
> The attached adds the ERROR, but puts that ereport() in the equivalent
> place as it is for CHECK constraints. There are arguments to improve
> the error message to include the constraint name here, or specify that
> we're talking about a NOT NULL constraint. I made it the same text as
> for CHECK, as that requires no new translations.
>
> David
>
> [1] https://www.postgresql.org/docs/devel/sql-createtable.html#SQL-CREATETABLE-PARMS-LIKE
> <v2-0001-Disallow-NOT-NULL-NO-INHERIT-constraints-on-parti.patch>

I agree that StoreRelNotNull() is a better place for the check.

When testing the patch, I noticed that the error message differs from the one produced when the constraint is declared directly in CREATE TABLE.

With v2:
```
evantest=# CREATE TABLE p (LIKE template) PARTITION BY LIST (a);
ERROR: cannot add NO INHERIT constraint to partitioned table "p"
```

With a direct constraint declaration:
```
evantest=# CREATE TABLE p1 (a integer, b integer CONSTRAINT badnn NOT NULL NO INHERIT) PARTITION BY LIST (a);
ERROR: not-null constraints on partitioned tables cannot be NO INHERIT
```

Should we keep these error messages consistent as they are actually the same error? Reusing the latter error message should not require new translations. Maybe we could also make the error codes consisten. The existing parser check uses ERRCODE_FEATURE_NOT_SUPPORTED, while v2 uses ERRCODE_INVALID_TABLE_DEFINITION.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/