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

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