Date: Tue, 5 Nov 2019 12:13:06 -0500
From: Bruce Momjian <bruce@momjian.us>
To: Tuomas Leikola <tuomas.leikola@gmail.com>
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: uniqueness and null could benefit from a hint for dba
Message-ID: <20191105171306.GA5839@momjian.us>
References: <156760275564.1127.12321702656456074572@wrigleys.postgresql.org>
 <20190927163747.GE31412@momjian.us>
 <CALhc4GAq4H9DSeC7tU6Nkr34zykiA4g8WQ+suWmXSXTDApgHEA@mail.gmail.com>
 <20191024143144.GE8650@momjian.us>
 <CALhc4GA=Bxs-w5Fo9x301-sWbBOcC7eQW=wTEEYUQhA1vA_Chg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="T4sUOijqQbZv57TR"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CALhc4GA=Bxs-w5Fo9x301-sWbBOcC7eQW=wTEEYUQhA1vA_Chg@mail.gmail.com>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk


--T4sUOijqQbZv57TR
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On Tue, Oct 29, 2019 at 02:00:38PM +0200, Tuomas Leikola wrote:
> On Thu, Oct 24, 2019 at 5:31 PM Bruce Momjian <bruce@momjian.us> wrote:
> 
>     Uh, I am wondering if it is just too details for our docs.  Can you
>     think of some text and its location?
> 
> 
> 
> "Unique indexes on functions can be used to create special types of
> constraints, like considering unique values equal (coalesce) or only allowing a
> single unique integer value of a float column (floor). A filtered unique index
> only enforces uniqueness on the subset of rows that match the filter."
> 
> I guess this would append to the paragraph "When an index is declared unique,
> multiple table rows with equal indexed values are not allowed. Null values are
> not considered equal. A multicolumn unique index will only reject cases where
> all indexed columns are equal in multiple rows.". Links to the mentioned
> keywords and concepts would of course be helpful, but not strictly necessary.
> 
> I also thought of mentioning the caveat of (un)scannability of function or
> filtered indexes but maybe that is a bit much.

I have reviewed our documentation and found approrpiate places to
mention your floor() example, and my IS NULL example.  Patch attached.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

--T4sUOijqQbZv57TR
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="unique.diff"

diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
new file mode 100644
index 95c0a19..c54bf0d
*** a/doc/src/sgml/indices.sgml
--- b/doc/src/sgml/indices.sgml
*************** CREATE INDEX test1_lower_col1_idx ON tes
*** 706,711 ****
--- 706,720 ----
    </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,
     as well as rows whose <literal>col1</literal> values are actually identical.
*************** CREATE UNIQUE INDEX tests_success_constr
*** 946,951 ****
--- 955,970 ----
      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>
+    </para>
    </example>
  
    <para>

--T4sUOijqQbZv57TR--