On Sunday, February 18, 2024, Darryl Green <
darryl.green@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.