public inbox for [email protected]
help / color / mirror / Atom feedpgsql: doc: add examples of creative use of unique expression indexes
9+ messages / 2 participants
[nested] [flat]
* pgsql: doc: add examples of creative use of unique expression indexes
@ 2019-12-27 19:49 Bruce Momjian <[email protected]>
2020-04-10 15:30 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Bruce Momjian @ 2019-12-27 19:49 UTC (permalink / raw)
To: [email protected]
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/[email protected]
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(+)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pgsql: doc: add examples of creative use of unique expression indexes
2019-12-27 19:49 pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
@ 2020-04-10 15:30 ` Tom Lane <[email protected]>
2020-04-10 21:48 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2020-04-10 15:30 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: [email protected]
Bruce Momjian <[email protected]> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pgsql: doc: add examples of creative use of unique expression indexes
2019-12-27 19:49 pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-10 15:30 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
@ 2020-04-10 21:48 ` Bruce Momjian <[email protected]>
2020-04-10 23:21 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Bruce Momjian @ 2020-04-10 21:48 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected]
On Fri, Apr 10, 2020 at 11:30:34AM -0400, Tom Lane wrote:
> Bruce Momjian <[email protected]> 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 <[email protected]> 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 +
Attachments:
[text/x-diff] index.diff (1.6K, 2-index.diff)
download | inline diff:
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 1be209a2fe..3a8f93bac7 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -705,15 +705,6 @@ CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
</programlisting>
</para>
- <para>
- Expression indexes also allow control over the scope of unique indexes.
- For example, this unique index prevents duplicate integer values from
- being stored in a <type>double precision</type>-typed column:
-<programlisting>
-CREATE UNIQUE INDEX test1_uniq_int ON tests ((floor(double_col)))
-</programlisting>
- </para>
-
<para>
If we were to declare this index <literal>UNIQUE</literal>, it would prevent
creation of rows whose <literal>col1</literal> values differ only in case,
@@ -953,17 +944,9 @@ CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
WHERE success;
</programlisting>
This is a particularly efficient approach when there are few
- successful tests and many unsuccessful ones.
- </para>
-
- <para>
- This index allows only one null in the indexed column by using a
- partial index clause to process only null column values, and using
- an expression index clause to index <literal>true</literal> instead
- of <literal>null</literal>:
-<programlisting>
-CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;
-</programlisting>
+ successful tests and many unsuccessful ones. Creating a unique
+ index with an <literal>IS NULL</literal> qualification can restrict
+ a column to a single <literal>NULL</literal> value.
</para>
</example>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pgsql: doc: add examples of creative use of unique expression indexes
2019-12-27 19:49 pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-10 15:30 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
2020-04-10 21:48 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
@ 2020-04-10 23:21 ` Tom Lane <[email protected]>
2020-04-11 00:17 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2020-04-10 23:21 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: [email protected]
Bruce Momjian <[email protected]> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pgsql: doc: add examples of creative use of unique expression indexes
2019-12-27 19:49 pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-10 15:30 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
2020-04-10 21:48 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-10 23:21 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
@ 2020-04-11 00:17 ` Bruce Momjian <[email protected]>
2020-04-11 12:25 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Bruce Momjian @ 2020-04-11 00:17 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected]
On Fri, Apr 10, 2020 at 07:21:29PM -0400, Tom Lane wrote:
> Bruce Momjian <[email protected]> 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 <[email protected]> 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 +
Attachments:
[text/x-diff] index.diff (1.8K, 2-index.diff)
download | inline diff:
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 1be209a2fe..e8205a07b5 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -705,15 +705,6 @@ CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
</programlisting>
</para>
- <para>
- Expression indexes also allow control over the scope of unique indexes.
- For example, this unique index prevents duplicate integer values from
- being stored in a <type>double precision</type>-typed column:
-<programlisting>
-CREATE UNIQUE INDEX test1_uniq_int ON tests ((floor(double_col)))
-</programlisting>
- </para>
-
<para>
If we were to declare this index <literal>UNIQUE</literal>, it would prevent
creation of rows whose <literal>col1</literal> values differ only in case,
@@ -956,16 +947,17 @@ CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
successful tests and many unsuccessful ones.
</para>
- <para>
- This index allows only one null in the indexed column by using a
- partial index clause to process only null column values, and using
- an expression index clause to index <literal>true</literal> instead
- of <literal>null</literal>:
+ </example>
+
+ <para>
+ This index allows only one null in the indexed column by using a
+ partial index clause to process only null column values, and using
+ an expression index clause to index <literal>true</literal> instead
+ of <literal>null</literal>:
<programlisting>
-CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;
+CREATE UNIQUE INDEX tests_target_one_null ON tests ((true)) WHERE target IS NULL;
</programlisting>
- </para>
- </example>
+ </para>
<para>
Finally, a partial index can also be used to override the system's
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pgsql: doc: add examples of creative use of unique expression indexes
2019-12-27 19:49 pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-10 15:30 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
2020-04-10 21:48 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-10 23:21 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
2020-04-11 00:17 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
@ 2020-04-11 12:25 ` Bruce Momjian <[email protected]>
2020-04-20 20:21 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Bruce Momjian @ 2020-04-11 12:25 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-docs
On Fri, Apr 10, 2020 at 08:17:09PM -0400, Bruce Momjian wrote:
> On Fri, Apr 10, 2020 at 07:21:29PM -0400, Tom Lane wrote:
> > Bruce Momjian <[email protected]> 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.
[ thread moved to docs]
I now remember that I wrote the first IS NULL in:
CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;
in hope that if someone is looking for the null value in the column, the
IS NULL would allow the index to be used to find it, while 1 or true
would not.
Also, I think the most popular use for this ability would be for
multi-column indexes where you want only one NULL value for a
combination of columns, e.g.:
CREATE UNIQUE INDEX tests_target_one_null ON test (x, (y IS NULL)) WHERE y IS NULL;
I have added that. It also hows the use of columns and expressions in
the same index. Proposed patch attached.
--
Bruce Momjian <[email protected]> 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 +
Attachments:
[text/x-diff] index.diff (1.9K, 2-index.diff)
download | inline diff:
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 1be209a2fe..2790fb2a89 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -705,15 +705,6 @@ CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
</programlisting>
</para>
- <para>
- Expression indexes also allow control over the scope of unique indexes.
- For example, this unique index prevents duplicate integer values from
- being stored in a <type>double precision</type>-typed column:
-<programlisting>
-CREATE UNIQUE INDEX test1_uniq_int ON tests ((floor(double_col)))
-</programlisting>
- </para>
-
<para>
If we were to declare this index <literal>UNIQUE</literal>, it would prevent
creation of rows whose <literal>col1</literal> values differ only in case,
@@ -956,16 +947,22 @@ CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
successful tests and many unsuccessful ones.
</para>
- <para>
- This index allows only one null in the indexed column by using a
- partial index clause to process only null column values, and using
- an expression index clause to index <literal>true</literal> instead
- of <literal>null</literal>:
+ </example>
+
+ <para>
+ This index allows only one null in the indexed column by using a
+ partial index clause to process only null column values, and using
+ an expression index clause to index <literal>true</literal> instead
+ of <literal>null</literal>:
<programlisting>
CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;
</programlisting>
- </para>
- </example>
+ This index allows only one null value for each value in the first
+ referenced column:
+<programlisting>
+CREATE UNIQUE INDEX tests_combo_one_null ON tests (col1, (target IS NULL)) WHERE target IS NULL;
+</programlisting>
+ </para>
<para>
Finally, a partial index can also be used to override the system's
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pgsql: doc: add examples of creative use of unique expression indexes
2019-12-27 19:49 pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-10 15:30 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
2020-04-10 21:48 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-10 23:21 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
2020-04-11 00:17 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-11 12:25 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
@ 2020-04-20 20:21 ` Tom Lane <[email protected]>
2020-04-21 00:31 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2020-04-20 20:21 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: pgsql-docs
[ sorry, I'd lost track of this thread ]
Bruce Momjian <[email protected]> writes:
> I now remember that I wrote the first IS NULL in:
> CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;
> in hope that if someone is looking for the null value in the column, the
> IS NULL would allow the index to be used to find it, while 1 or true
> would not.
Well, that's not the case:
regression=# create index tenk1_null_index on tenk1((1)) where ten is null;
CREATE INDEX
regression=# explain select * from tenk1 where ten is null;
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using tenk1_null_index on tenk1 (cost=0.12..8.14 rows=1 width=244)
(1 row)
(Maybe it was true at some time in the past, but not any more.)
Also, it complicates the example, and since you didn't explain the
reason for the complication, I think it's pretty confusing.
But really I still don't see the need for these additional examples
at all. It's especially weird that what you want to do is have
some examples on that page have <example> markup and others not.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pgsql: doc: add examples of creative use of unique expression indexes
2019-12-27 19:49 pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-10 15:30 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
2020-04-10 21:48 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-10 23:21 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
2020-04-11 00:17 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-11 12:25 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-20 20:21 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
@ 2020-04-21 00:31 ` Bruce Momjian <[email protected]>
2020-05-21 23:49 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Bruce Momjian @ 2020-04-21 00:31 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-docs
On Mon, Apr 20, 2020 at 04:21:32PM -0400, Tom Lane wrote:
> [ sorry, I'd lost track of this thread ]
>
> Bruce Momjian <[email protected]> writes:
> > I now remember that I wrote the first IS NULL in:
> > CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;
> > in hope that if someone is looking for the null value in the column, the
> > IS NULL would allow the index to be used to find it, while 1 or true
> > would not.
>
> Well, that's not the case:
>
> regression=# create index tenk1_null_index on tenk1((1)) where ten is null;
> CREATE INDEX
> regression=# explain select * from tenk1 where ten is null;
> QUERY PLAN
> --------------------------------------------------------------------------------
> Index Scan using tenk1_null_index on tenk1 (cost=0.12..8.14 rows=1 width=244)
> (1 row)
>
> (Maybe it was true at some time in the past, but not any more.)
>
> Also, it complicates the example, and since you didn't explain the
> reason for the complication, I think it's pretty confusing.
>
> But really I still don't see the need for these additional examples
> at all. It's especially weird that what you want to do is have
> some examples on that page have <example> markup and others not.
OK, seems like only you and I care about this issue, which I take to
mean that we should minimize what we are adding here. What the attached
patch does is to remove the previous commit, and just add a sentence to
the last example to mention the ability restrict a column to a single
NULL.
--
Bruce Momjian <[email protected]> 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 +
Attachments:
[text/x-diff] index.diff (1.6K, 2-index.diff)
download | inline diff:
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 1be209a2fe..13285bb0dd 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -705,15 +705,6 @@ CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
</programlisting>
</para>
- <para>
- Expression indexes also allow control over the scope of unique indexes.
- For example, this unique index prevents duplicate integer values from
- being stored in a <type>double precision</type>-typed column:
-<programlisting>
-CREATE UNIQUE INDEX test1_uniq_int ON tests ((floor(double_col)))
-</programlisting>
- </para>
-
<para>
If we were to declare this index <literal>UNIQUE</literal>, it would prevent
creation of rows whose <literal>col1</literal> values differ only in case,
@@ -953,18 +944,11 @@ CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
WHERE success;
</programlisting>
This is a particularly efficient approach when there are few
- successful tests and many unsuccessful ones.
+ successful tests and many unsuccessful ones. It is also possible to
+ allow only one null in a column by creating a unique partial index
+ with an <literal>IS NULL</literal> restriction.
</para>
- <para>
- This index allows only one null in the indexed column by using a
- partial index clause to process only null column values, and using
- an expression index clause to index <literal>true</literal> instead
- of <literal>null</literal>:
-<programlisting>
-CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;
-</programlisting>
- </para>
</example>
<para>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: pgsql: doc: add examples of creative use of unique expression indexes
2019-12-27 19:49 pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-10 15:30 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
2020-04-10 21:48 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-10 23:21 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
2020-04-11 00:17 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-11 12:25 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-20 20:21 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
2020-04-21 00:31 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
@ 2020-05-21 23:49 ` Bruce Momjian <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Bruce Momjian @ 2020-05-21 23:49 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-docs
On Mon, Apr 20, 2020 at 08:31:29PM -0400, Bruce Momjian wrote:
> On Mon, Apr 20, 2020 at 04:21:32PM -0400, Tom Lane wrote:
> > [ sorry, I'd lost track of this thread ]
> >
> > Bruce Momjian <[email protected]> writes:
> > > I now remember that I wrote the first IS NULL in:
> > > CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;
> > > in hope that if someone is looking for the null value in the column, the
> > > IS NULL would allow the index to be used to find it, while 1 or true
> > > would not.
> >
> > Well, that's not the case:
> >
> > regression=# create index tenk1_null_index on tenk1((1)) where ten is null;
> > CREATE INDEX
> > regression=# explain select * from tenk1 where ten is null;
> > QUERY PLAN
> > --------------------------------------------------------------------------------
> > Index Scan using tenk1_null_index on tenk1 (cost=0.12..8.14 rows=1 width=244)
> > (1 row)
> >
> > (Maybe it was true at some time in the past, but not any more.)
> >
> > Also, it complicates the example, and since you didn't explain the
> > reason for the complication, I think it's pretty confusing.
> >
> > But really I still don't see the need for these additional examples
> > at all. It's especially weird that what you want to do is have
> > some examples on that page have <example> markup and others not.
>
> OK, seems like only you and I care about this issue, which I take to
> mean that we should minimize what we are adding here. What the attached
> patch does is to remove the previous commit, and just add a sentence to
> the last example to mention the ability restrict a column to a single
> NULL.
Patch applied.
--
Bruce Momjian <[email protected]> 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 +
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2020-05-21 23:49 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-12-27 19:49 pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-10 15:30 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
2020-04-10 21:48 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-10 23:21 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
2020-04-11 00:17 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-11 12:25 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-04-20 20:21 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <[email protected]>
2020-04-21 00:31 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
2020-05-21 23:49 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox