Re: Partitioning, Identity and Uniqueness (given pg 16 changes) - Mailing list pgsql-general

From David G. Johnston
Subject Re: Partitioning, Identity and Uniqueness (given pg 16 changes)
Date
Msg-id CAKFQuwYVwa462Lq3RiiuNz+SCaYtC0pEg5AirQ_s9Z4tJRzsnw@mail.gmail.com
Whole thread Raw
In response to Partitioning, Identity and Uniqueness (given pg 16 changes)  (Darryl Green <darryl.green@gmail.com>)
List pgsql-general
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.


pgsql-general by date:

Previous
From: Darryl Green
Date:
Subject: Partitioning, Identity and Uniqueness (given pg 16 changes)
Next
From: David Rowley
Date:
Subject: Re: Partitioning, Identity and Uniqueness (given pg 16 changes)