On Wed, 21 Oct 2026 at 21:32, Nitin Motiani <[email protected]> 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
Description: Binary data
