On Mon, 19 Feb 2024 at 16:32, Darryl Green <darryl.green@xxxxxxxxx> wrote: > 2) It would be nice to be able to specify the id as pk on the table being partitioned (as it was in the non-partitioned definition of the table) once to document and enforce that the partitions simply inherit the id pk. This would seem only to need the "partition by" validation to allow a column not mentioned in partition by clause to be defined as pk or unique if and only if the pk/unique column is an identity column. Not a big deal but is this practical/valid? Unfortunately, it's not as easy as you think. It's not a matter of dropping the check that requires all PRIMARY KEY columns are present in the PARTITION BY clause. For this to work a *single* index (i.e. non-partitioned index) would have to index all partitions. Otherwise, how would Postgres ensure that the value being inserted doesn't exist in some other partition? The problem with a single index is that it kinda defeats the purpose of partitioning, i.e., "my table is large and I want to split it up". Operations such as DETACH PARTITION would have to become more than just a metadata operation when you consider having to trawl through the index and remove all records belonging to a single partition. It may be possible to still have it work by doing a speculative record in the index for the target table then go and check all of the other indexes before marking the speculative entry as valid. I think it would be very tricky to make it work well, however. Imagine how expensive ATTACH PARTITION would be! There are probably other race conditions I've not thought about too. Likely, we'd get more complaints about this being a terrible feature than we do due to the fact that it's unsupported. David
