Thread: Re: pgsql: doc: add examples of creative use of unique expressionindexes
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 +
Attachment
[ 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
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 +
Attachment
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 +