Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

On Sunday, February 18, 2024, Darryl Green <darryl(dot)green(at)gmail(dot)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.