pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedExample for Unique Partial Indexes
4+ messages / 3 participants
[nested] [flat]
* Example for Unique Partial Indexes
@ 2023-11-07 12:53 PG Doc comments form <noreply@postgresql.org>
2023-11-07 16:48 ` Re: Example for Unique Partial Indexes David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: PG Doc comments form @ 2023-11-07 12:53 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: m.matin.agh@gmail.com
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Example for Unique Partial Indexes
2023-11-07 12:53 Example for Unique Partial Indexes PG Doc comments form <noreply@postgresql.org>
@ 2023-11-07 16:48 ` David G. Johnston <david.g.johnston@gmail.com>
2023-11-07 17:08 ` Re: Example for Unique Partial Indexes Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2023-11-07 16:48 UTC (permalink / raw)
To: m.matin.agh@gmail.com; pgsql-docs@lists.postgresql.org
On Tue, Nov 7, 2023, 09:25 PG Doc comments form <noreply@postgresql.org>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Example for Unique Partial Indexes
2023-11-07 12:53 Example for Unique Partial Indexes PG Doc comments form <noreply@postgresql.org>
2023-11-07 16:48 ` Re: Example for Unique Partial Indexes David G. Johnston <david.g.johnston@gmail.com>
@ 2023-11-07 17:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-11-07 18:14 ` Re: Example for Unique Partial Indexes David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2023-11-07 17:08 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: m.matin.agh@gmail.com; pgsql-docs@lists.postgresql.org
"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.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Example for Unique Partial Indexes
2023-11-07 12:53 Example for Unique Partial Indexes PG Doc comments form <noreply@postgresql.org>
2023-11-07 16:48 ` Re: Example for Unique Partial Indexes David G. Johnston <david.g.johnston@gmail.com>
2023-11-07 17:08 ` Re: Example for Unique Partial Indexes Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-11-07 18:14 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2023-11-07 18:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: m.matin.agh@gmail.com; pgsql-docs@lists.postgresql.org
On Tue, Nov 7, 2023 at 10:08 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "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."
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-11-07 18:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-07 12:53 Example for Unique Partial Indexes PG Doc comments form <noreply@postgresql.org>
2023-11-07 16:48 ` David G. Johnston <david.g.johnston@gmail.com>
2023-11-07 17:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-11-07 18:14 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox