Thread: 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 +
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.
On Fri, Sep 27, 2019 at 7:37 PM Bruce Momjian <bruce@momjian.us> wrote:
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 +
- Tuomas
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 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
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 +
Attachment
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 +