On Sunday, February 18, 2024, Darryl Green <darryl.gr...@gmail.com> wrote:
> > I note that in Postgresql 16 identity column handling in partitioned > tables has been aligned to the view that the partitioned table as a whole > is a single relation (and so a unique identity across partitions). This > makes sense. > Where did you find this documented? It makes sense as that is indeed how a partitioned table should behave but given the constraint definitely wasn’t a foregone conclusion not needing documentation. > Now, I want to partition but not by that identity column. But I have to > include the identity in the partitioning key (if I have it as the pk) so > that the uniqueness can be checked. But - using a (shared across all > partition tables) identity sequence should be enough to ensure uniqueness > without this. > A sequences is a value generator - its state can be modified so that numbers it previously issued are issued again. Thus a sequence implies nothing about whether the thing using it is unique. You need a constraint to do that and as you note unique constraints can only enforce a single partition. In short, you can choose to rely on the expected usage/behavior of a sequence defined this way and forgo the enforced PK constraint. David J.
