Thread: pgsql: doc: add examples of creative use of unique expression indexes
doc: add examples of creative use of unique expression indexes Unique expression indexes can constrain data in creative ways, so show two examples. Reported-by: Tuomas Leikola Discussion: https://postgr.es/m/156760275564.1127.12321702656456074572@wrigleys.postgresql.org Backpatch-through: 9.4 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/a9760d0f3cb523336b5fdd9d6c5985e39a8588a1 Modified Files -------------- doc/src/sgml/indices.sgml | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+)
Bruce Momjian <bruce@momjian.us> writes: > doc: add examples of creative use of unique expression indexes > https://git.postgresql.org/pg/commitdiff/a9760d0f3cb523336b5fdd9d6c5985e39a8588a1 We had a complaint [1] that this dropped an example into the middle of two related paragraphs. I agree with that objection, and also notice that the extra example broke subsequent references to the "first example" and "second example". I'm also unhappy that the other addition that this commit made was dropped inside Example 11.3; if we're going to use <example> markup at all, each one ought to be a coherent entity. On top of that, I don't find that either example actually adds anything to the discussion, as the same points are being made in the existing text. Therefore, I don't think it's worth trying to fix these problems, and propose just reverting this patch. regards, tom lane [1] https://www.postgresql.org/message-id/158648685043.655.3074746555320970574%40wrigleys.postgresql.org
On Fri, Apr 10, 2020 at 11:30:34AM -0400, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > doc: add examples of creative use of unique expression indexes > > https://git.postgresql.org/pg/commitdiff/a9760d0f3cb523336b5fdd9d6c5985e39a8588a1 > > We had a complaint [1] that this dropped an example into the middle of > two related paragraphs. I agree with that objection, and also notice > that the extra example broke subsequent references to the "first example" > and "second example". I'm also unhappy that the other addition that this > commit made was dropped inside Example 11.3; if we're going to use > <example> markup at all, each one ought to be a coherent entity. > > On top of that, I don't find that either example actually adds anything > to the discussion, as the same points are being made in the existing > text. Therefore, I don't think it's worth trying to fix these problems, > and propose just reverting this patch. > > regards, tom lane > > [1] https://www.postgresql.org/message-id/158648685043.655.3074746555320970574%40wrigleys.postgresql.org I agree with your analysis. I still want to have some mention that partial indexes can be used to create single-NULL columns, which might be required for compatibility with other databases. Attached is an updated patch which removes the previous commit but adds a mention of this. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Attachment
Bruce Momjian <bruce@momjian.us> writes:
> I agree with your analysis. I still want to have some mention that
> partial indexes can be used to create single-NULL columns, which might
> be required for compatibility with other databases. Attached is an
> updated patch which removes the previous commit but adds a mention of
> this.
The single-null thing is probably a useful example, but please make
it an actual separate example, or at least its own para outside the
existing <example> sections.
Also, the existing example demonstrating that seems overcomplicated;
why not just
create unique index ... (1) where (foo is null);
regards, tom lane
On Fri, Apr 10, 2020 at 07:21:29PM -0400, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > I agree with your analysis. I still want to have some mention that > > partial indexes can be used to create single-NULL columns, which might > > be required for compatibility with other databases. Attached is an > > updated patch which removes the previous commit but adds a mention of > > this. > > The single-null thing is probably a useful example, but please make > it an actual separate example, or at least its own para outside the > existing <example> sections. > > Also, the existing example demonstrating that seems overcomplicated; > why not just > > create unique index ... (1) where (foo is null); I ended up using "true" since that is ony one byte; patch attached. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +