"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Tue, Nov 7, 2023, 09:25 PG Doc comments form <noreply@postgresql.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."