On 2024-May-06, Justin Pryzby wrote: > > (Do you really want the partition to be > > created without the primary key already there?) > > Why not ? The PK will be added when I attach it one moment later. > > CREATE TABLE part (LIKE parent); > ALTER TABLE parent ATTACH PARTITION part ...
Well, if you load data in the meantime, you'll spend time during `ALTER TABLE parent` for the index to be created. (On the other hand, you may want to first create the table, then load data, then create the indexes.) > Do you really think that after ATTACH, the constraints should be > different depending on whether the child was created INCLUDING INDEXES ? > I'll continue to think about this, but I still find that surprising. I don't think I have a choice about this, because the standard says that the resulting table must have NOT NULL on all columns which have a nullability characteristic is known not nullable; and the primary key forces that to be the case. Thinking again, maybe this is wrong in the opposite direction: perhaps we should have not-null constraints on those columns even if INCLUDING CONSTRAINTS is given, because failing to do that (which is the current behavior) is non-conformant. In turn, this suggests that in order to make the partitioning behavior consistent, we should _in addition_ make CREATE TABLE PARTITION OF add explicit not-null constraints to the columns of the primary key of the partitioned table. This would also solve your complaint, because then the table would have the not-null constraint in all cases. This might be taking the whole affair too far, though; not sure. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "We’ve narrowed the problem down to the customer’s pants being in a situation of vigorous combustion" (Robert Haas, Postgres expert extraordinaire)