public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Bruce Momjian <[email protected]>
Cc: PostgreSQL-documentation <[email protected]>
Subject: Re: pgsql: doc: add examples of creative use of unique expression indexes
Date: Mon, 20 Apr 2020 16:21:32 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

[ 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

view thread (9+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: pgsql: doc: add examples of creative use of unique expression indexes
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox