Re: Example for Unique Partial Indexes
On Tue, Nov 7, 2023 at 10:08 AM Tom Lane wrote: > "David G. Johnston" writes: > > On Tue, Nov 7, 2023, 09:25 PG Doc comments form > > 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.
Re: Example for Unique Partial Indexes
"David G. Johnston" writes: > On Tue, Nov 7, 2023, 09:25 PG Doc comments form > 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. regards, tom lane
Re: Example for Unique Partial Indexes
On Tue, Nov 7, 2023, 09:25 PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/indexes-unique.html > Description: > > I would like to request an improvement to the PostgreSQL documentation, > specifically in the sections "11.6. Unique Indexes" and "11.8. Partial > Indexes." > > 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. David J.
Example for Unique Partial Indexes
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/indexes-unique.html Description: I would like to request an improvement to the PostgreSQL documentation, specifically in the sections "11.6. Unique Indexes" and "11.8. Partial Indexes." 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. To enhance the documentation's completeness and to provide a clear example for users, I propose adding an entry in one of these sections with an example as follows: ```sql Copy code -- Example of Creating a Unique Partial Index -- This index enforces uniqueness of the 'column' over a subset of the table CREATE UNIQUE INDEX name ON table (column [, ...]) WHERE (condition); This addition will assist users in understanding how to create unique partial indexes and help them enforce uniqueness over specific subsets of their tables. Please consider incorporating this addition into the documentation. ``` Thank you for your attention to this request, and I believe it will contribute to the clarity and comprehensiveness of the PostgreSQL documentation.