MIME-Version: 1.0
References: <156760275564.1127.12321702656456074572@wrigleys.postgresql.org>
 <20190927163747.GE31412@momjian.us>
 <CALhc4GAq4H9DSeC7tU6Nkr34zykiA4g8WQ+suWmXSXTDApgHEA@mail.gmail.com>
 <20191024143144.GE8650@momjian.us>
In-Reply-To: <20191024143144.GE8650@momjian.us>
From: Tuomas Leikola <tuomas.leikola@gmail.com>
Date: Tue, 29 Oct 2019 14:00:38 +0200
Message-ID: 
 <CALhc4GA=Bxs-w5Fo9x301-sWbBOcC7eQW=wTEEYUQhA1vA_Chg@mail.gmail.com>
Subject: Re: uniqueness and null could benefit from a hint for dba
To: Bruce Momjian <bruce@momjian.us>
Cc: pgsql-docs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000ff99af05960b5d55"
Precedence: bulk

--000000000000ff99af05960b5d55
Content-Type: text/plain; charset="UTF-8"

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.

-- 
- Tuomas

--000000000000ff99af05960b5d55
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Oct 24, 2019 at 5:31 PM Bruce Mom=
jian &lt;<a href=3D"mailto:bruce@momjian.us">bruce@momjian.us</a>&gt; wrote=
:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddi=
ng-left:1ex">Uh, I am wondering if it is just too details for our docs.=C2=
=A0 Can you<br>
think of some text and its location?<br>
<br></blockquote></div><div><br></div><div>&quot;Unique indexes on function=
s can be used to create special types of constraints, like considering uniq=
ue values equal (coalesce) or only allowing a single unique integer value o=
f a float column (floor). A filtered unique index only enforces uniqueness =
on the subset of rows that match the filter.&quot;</div><div><br></div><div=
>I guess this would append to the paragraph &quot;<span style=3D"color:rgb(=
13,10,11);font-family:&quot;Open Sans&quot;,sans-serif;font-size:14.4px">Wh=
en an index is declared unique, multiple table rows with equal indexed valu=
es are not allowed. Null values are not considered equal. A multicolumn uni=
que index will only reject cases where all indexed columns are equal in mul=
tiple rows.&quot;. Links to the mentioned keywords=C2=A0and concepts would =
of course be helpful, but not strictly necessary.</span></div><div><span st=
yle=3D"color:rgb(13,10,11);font-family:&quot;Open Sans&quot;,sans-serif;fon=
t-size:14.4px"><br></span></div><div><font color=3D"#0d0a0b" face=3D"Open S=
ans, sans-serif"><span style=3D"font-size:14.4px">I also thought of mention=
ing the caveat of (un)scannability of function or filtered indexes but mayb=
e that is a bit much.</span></font></div><div><br></div>-- <br><div dir=3D"=
ltr" class=3D"gmail_signature">- Tuomas</div></div>

--000000000000ff99af05960b5d55--