Re: Example for Unique Partial Indexes

On Tue, Nov 7, 2023 at 10:08 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Tue, Nov 7, 2023, 09:25 PG Doc comments form <noreply(at)postgresql(dot)org>
> > wrote:
> >> Currently, the documentation does not provide information on how to
> create
> >> unique partial indexes. Unique partial indexes are valuable for
> enforcing
> >> uniqueness of a column's value over a subset of a table
>
> > 11.8 discusses this in detail including an example. Maybe should add a
> > forward reference from 11.6 though.
>
> Yeah, AFAICS 11.8's coverage of this point is perfectly adequate.
>
> I'm disinclined to add a forward reference, because 11.8 is the
> first section that mentions partial indexes at all. Somebody
> reading the chapter in order would have no idea what we were
> talking about.
>
>
I do understand that argument but given that uniqueness comes up
considerably more often than partial indexes for me it makes sense to
inform the reader learning about unique constraints about what else exists
in the world and not just presume they are reading the documentation
serially (or will take the time to really dive into 11.8).

"Sometimes you only care about uniqueness for data within a particular
state (say an active state) and do not care if (say, inactive) data exists
as well, possibly multiple times. A unique index by itself cannot handle
this situation but you can combine it with a partial index for the active
state data (as described in 11.8) to achieve this limited uniqueness
requirement."

David J.