Обсуждение: uniqueness and null could benefit from a hint for dba

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/11/indexes-unique.html
Description:

Sometimes it is convenient to create an unique index that considers NULL
values equal. Designating a "zero" value for those rows might not be
feasible, for example due to a foreign key.

The documentation currently only states that unique indexes do not consider
NULLs equal. It might be good to offer workarounds, like indexing a coalesce
function, if scans are not the reason for the index, but the uniqueness
constraint.

On Wed, Sep  4, 2019 at 01:12:35PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
> 
> Page: https://www.postgresql.org/docs/11/indexes-unique.html
> Description:
> 
> Sometimes it is convenient to create an unique index that considers NULL
> values equal. Designating a "zero" value for those rows might not be
> feasible, for example due to a foreign key.
> 
> The documentation currently only states that unique indexes do not consider
> NULLs equal. It might be good to offer workarounds, like indexing a coalesce
> function, if scans are not the reason for the index, but the uniqueness
> constraint.

I did write a blog entry about this:

    https://momjian.us/main/blogs/pgblog/2017.html#April_3_2017

Does that help?

-- 
  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 +

On Wed, Oct 23, 2019 at 02:35:02PM +0300, Tuomas Leikola wrote:
> That is a nice design. You can create a regular unique index where columns(s)s
> are not null and then filtered index(es) for the cases where some of the unique
> columns are null.
> 
> However my point, specifically, was that if the document in question would have
> offered alternative solutions, I personally would have been saved from some
> frustration and an exercise in bad index design (I had 5 nullables that need
> uniqueness for the null as well). Maybe it would help someone else.

Uh, I am wondering if it is just too details for our docs.  Can you
think of some text and its location?

-- 
  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 +

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 +

On Tue, Nov  5, 2019 at 12:13:06PM -0500, Bruce Momjian wrote:
> 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.

Patch applied back through 9.4.  Thanks.

-- 
  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 +