pg.ddx.io pgsql-committers@postgresql.org mailing list archive
help / color / mirror / Atom feedpgsql: doc: add examples of creative use of unique expression indexes
15+ messages / 2 participants
[nested] [flat]
Commits from this thread:
2020-05-21 c20fd088f1 388d7f8c62 00c51b365c d594e4cd73 c7c0db90c2 c7aaa04d78 doc: Simplify mention of unique indexes for NULL control
* pgsql: doc: add examples of creative use of unique expression indexes
@ 2019-12-27 19:49 Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 15+ messages in thread
From: Bruce Momjian @ 2019-12-27 19:49 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
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
------
REL9_4_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/645694731ca9526bfcb976ae7be17b9bd8de6294
Modified Files
--------------
doc/src/sgml/indices.sgml | 19 +++++++++++++++++++
1 file changed, 19 insertions(+)
^ permalink raw reply [nested|flat] 15+ messages in thread
* pgsql: doc: add examples of creative use of unique expression indexes
@ 2019-12-27 19:49 Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 15+ messages in thread
From: Bruce Momjian @ 2019-12-27 19:49 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
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
------
REL_11_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/e6cf1724d8cdf81b4ec7e613e1b41357834261f9
Modified Files
--------------
doc/src/sgml/indices.sgml | 19 +++++++++++++++++++
1 file changed, 19 insertions(+)
^ permalink raw reply [nested|flat] 15+ messages in thread
* pgsql: doc: add examples of creative use of unique expression indexes
@ 2019-12-27 19:49 Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 15+ messages in thread
From: Bruce Momjian @ 2019-12-27 19:49 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
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
------
REL9_5_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/937cf1b09146ef91195780f747900cf69e8d0fd8
Modified Files
--------------
doc/src/sgml/indices.sgml | 19 +++++++++++++++++++
1 file changed, 19 insertions(+)
^ permalink raw reply [nested|flat] 15+ messages in thread
* pgsql: doc: add examples of creative use of unique expression indexes
@ 2019-12-27 19:49 Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 15+ messages in thread
From: Bruce Momjian @ 2019-12-27 19:49 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
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(+)
^ permalink raw reply [nested|flat] 15+ messages in thread
* pgsql: doc: add examples of creative use of unique expression indexes
@ 2019-12-27 19:49 Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 15+ messages in thread
From: Bruce Momjian @ 2019-12-27 19:49 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
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
------
REL_10_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/2fc7fc48863e963516f47508468fd37aa8d2713a
Modified Files
--------------
doc/src/sgml/indices.sgml | 19 +++++++++++++++++++
1 file changed, 19 insertions(+)
^ permalink raw reply [nested|flat] 15+ messages in thread
* pgsql: doc: add examples of creative use of unique expression indexes
@ 2019-12-27 19:49 Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 15+ messages in thread
From: Bruce Momjian @ 2019-12-27 19:49 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
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
------
REL_12_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/2034bcc845f228c675c218e1928cf5337e74381d
Modified Files
--------------
doc/src/sgml/indices.sgml | 19 +++++++++++++++++++
1 file changed, 19 insertions(+)
^ permalink raw reply [nested|flat] 15+ messages in thread
* pgsql: doc: add examples of creative use of unique expression indexes
@ 2019-12-27 19:49 Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 15+ messages in thread
From: Bruce Momjian @ 2019-12-27 19:49 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
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
------
REL9_6_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/6f53e66bec7087b615cd5a5e94d10a83d301ba10
Modified Files
--------------
doc/src/sgml/indices.sgml | 19 +++++++++++++++++++
1 file changed, 19 insertions(+)
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: pgsql: doc: add examples of creative use of unique expression indexes
@ 2020-04-10 15:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2020-04-10 15:30 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-committers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: pgsql: doc: add examples of creative use of unique expression indexes
@ 2020-04-10 21:48 Bruce Momjian <bruce@momjian.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Bruce Momjian @ 2020-04-10 21:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-committers@lists.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 +
Attachments:
index.diff (text/x-diff, 1.6K) download
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>
* Re: pgsql: doc: add examples of creative use of unique expression indexes
@ 2020-04-10 23:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2020-04-10 23:21 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-committers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: pgsql: doc: add examples of creative use of unique expression indexes
@ 2020-04-11 00:17 Bruce Momjian <bruce@momjian.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Bruce Momjian @ 2020-04-11 00:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-committers@lists.postgresql.org
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 +
Attachments:
index.diff (text/x-diff, 1.8K) download
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
* Re: pgsql: doc: add examples of creative use of unique expression indexes
@ 2020-04-11 12:25 Bruce Momjian <bruce@momjian.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 15+ messages in thread
From: Bruce Momjian @ 2020-04-11 12:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL-documentation <pgsql-docs@postgresql.org>
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 <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.
[ 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 <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 +
Attachments:
index.diff (text/x-diff, 1.9K) download
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
* Re: pgsql: doc: add examples of creative use of unique expression indexes
@ 2020-04-20 20:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2020-04-20 20:21 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: PostgreSQL-documentation <pgsql-docs@postgresql.org>
[ sorry, I'd lost track of this thread ]
Bruce Momjian <bruce@momjian.us> 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] 15+ messages in thread
* Re: pgsql: doc: add examples of creative use of unique expression indexes
@ 2020-04-21 00:31 Bruce Momjian <bruce@momjian.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Bruce Momjian @ 2020-04-21 00:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL-documentation <pgsql-docs@postgresql.org>
On Mon, Apr 20, 2020 at 04:21:32PM -0400, Tom Lane wrote:
> [ sorry, I'd lost track of this thread ]
>
> Bruce Momjian <bruce@momjian.us> 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 <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 +
Attachments:
index.diff (text/x-diff, 1.6K) download
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>
* Re: pgsql: doc: add examples of creative use of unique expression indexes
@ 2020-05-21 23:49 Bruce Momjian <bruce@momjian.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 15+ messages in thread
From: Bruce Momjian @ 2020-05-21 23:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL-documentation <pgsql-docs@postgresql.org>
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 <bruce@momjian.us> 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 <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 +
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2020-05-21 23:49 UTC | newest]
Thread overview: 15+ 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 <bruce@momjian.us>
2020-04-10 15:30 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <tgl@sss.pgh.pa.us>
2020-04-10 21:48 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <bruce@momjian.us>
2020-04-10 23:21 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <tgl@sss.pgh.pa.us>
2020-04-11 00:17 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <bruce@momjian.us>
2020-04-11 12:25 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <bruce@momjian.us>
2020-04-20 20:21 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Tom Lane <tgl@sss.pgh.pa.us>
2020-04-21 00:31 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <bruce@momjian.us>
2020-05-21 23:49 ` Re: pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <bruce@momjian.us>
2019-12-27 19:49 pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <bruce@momjian.us>
2019-12-27 19:49 pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <bruce@momjian.us>
2019-12-27 19:49 pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <bruce@momjian.us>
2019-12-27 19:49 pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <bruce@momjian.us>
2019-12-27 19:49 pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <bruce@momjian.us>
2019-12-27 19:49 pgsql: doc: add examples of creative use of unique expression indexes Bruce Momjian <bruce@momjian.us>
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