public inbox for [email protected]help / color / mirror / Atom feed
uniqueness and null could benefit from a hint for dba 7+ messages / 3 participants [nested] [flat]
* uniqueness and null could benefit from a hint for dba @ 2019-09-04 13:12 PG Doc comments form <[email protected]> 2019-09-27 16:37 ` Re: uniqueness and null could benefit from a hint for dba Bruce Momjian <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: PG Doc comments form @ 2019-09-04 13:12 UTC (permalink / raw) To: [email protected]; +Cc: [email protected] 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. ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: uniqueness and null could benefit from a hint for dba 2019-09-04 13:12 uniqueness and null could benefit from a hint for dba PG Doc comments form <[email protected]> @ 2019-09-27 16:37 ` Bruce Momjian <[email protected]> 2019-10-23 11:35 ` Re: uniqueness and null could benefit from a hint for dba Tuomas Leikola <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Bruce Momjian @ 2019-09-27 16:37 UTC (permalink / raw) To: [email protected]; [email protected] 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 <[email protected]> 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 + ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: uniqueness and null could benefit from a hint for dba 2019-09-04 13:12 uniqueness and null could benefit from a hint for dba PG Doc comments form <[email protected]> 2019-09-27 16:37 ` Re: uniqueness and null could benefit from a hint for dba Bruce Momjian <[email protected]> @ 2019-10-23 11:35 ` Tuomas Leikola <[email protected]> 2019-10-24 14:31 ` Re: uniqueness and null could benefit from a hint for dba Bruce Momjian <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Tuomas Leikola @ 2019-10-23 11:35 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: [email protected] 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 <[email protected]> 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 <[email protected]> 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: uniqueness and null could benefit from a hint for dba 2019-09-04 13:12 uniqueness and null could benefit from a hint for dba PG Doc comments form <[email protected]> 2019-09-27 16:37 ` Re: uniqueness and null could benefit from a hint for dba Bruce Momjian <[email protected]> 2019-10-23 11:35 ` Re: uniqueness and null could benefit from a hint for dba Tuomas Leikola <[email protected]> @ 2019-10-24 14:31 ` Bruce Momjian <[email protected]> 2019-10-29 12:00 ` Re: uniqueness and null could benefit from a hint for dba Tuomas Leikola <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Bruce Momjian @ 2019-10-24 14:31 UTC (permalink / raw) To: Tuomas Leikola <[email protected]>; +Cc: [email protected] 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 <[email protected]> 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 + ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: uniqueness and null could benefit from a hint for dba 2019-09-04 13:12 uniqueness and null could benefit from a hint for dba PG Doc comments form <[email protected]> 2019-09-27 16:37 ` Re: uniqueness and null could benefit from a hint for dba Bruce Momjian <[email protected]> 2019-10-23 11:35 ` Re: uniqueness and null could benefit from a hint for dba Tuomas Leikola <[email protected]> 2019-10-24 14:31 ` Re: uniqueness and null could benefit from a hint for dba Bruce Momjian <[email protected]> @ 2019-10-29 12:00 ` Tuomas Leikola <[email protected]> 2019-11-05 17:13 ` Re: uniqueness and null could benefit from a hint for dba Bruce Momjian <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Tuomas Leikola @ 2019-10-29 12:00 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: [email protected] On Thu, Oct 24, 2019 at 5:31 PM Bruce Momjian <[email protected]> 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: uniqueness and null could benefit from a hint for dba 2019-09-04 13:12 uniqueness and null could benefit from a hint for dba PG Doc comments form <[email protected]> 2019-09-27 16:37 ` Re: uniqueness and null could benefit from a hint for dba Bruce Momjian <[email protected]> 2019-10-23 11:35 ` Re: uniqueness and null could benefit from a hint for dba Tuomas Leikola <[email protected]> 2019-10-24 14:31 ` Re: uniqueness and null could benefit from a hint for dba Bruce Momjian <[email protected]> 2019-10-29 12:00 ` Re: uniqueness and null could benefit from a hint for dba Tuomas Leikola <[email protected]> @ 2019-11-05 17:13 ` Bruce Momjian <[email protected]> 2019-12-27 19:49 ` Re: uniqueness and null could benefit from a hint for dba Bruce Momjian <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Bruce Momjian @ 2019-11-05 17:13 UTC (permalink / raw) To: Tuomas Leikola <[email protected]>; +Cc: [email protected] On Tue, Oct 29, 2019 at 02:00:38PM +0200, Tuomas Leikola wrote: > On Thu, Oct 24, 2019 at 5:31 PM Bruce Momjian <[email protected]> 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 <[email protected]> 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 + Attachments: [text/x-diff] unique.diff (1.5K, 2-unique.diff) download | inline 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> ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: uniqueness and null could benefit from a hint for dba 2019-09-04 13:12 uniqueness and null could benefit from a hint for dba PG Doc comments form <[email protected]> 2019-09-27 16:37 ` Re: uniqueness and null could benefit from a hint for dba Bruce Momjian <[email protected]> 2019-10-23 11:35 ` Re: uniqueness and null could benefit from a hint for dba Tuomas Leikola <[email protected]> 2019-10-24 14:31 ` Re: uniqueness and null could benefit from a hint for dba Bruce Momjian <[email protected]> 2019-10-29 12:00 ` Re: uniqueness and null could benefit from a hint for dba Tuomas Leikola <[email protected]> 2019-11-05 17:13 ` Re: uniqueness and null could benefit from a hint for dba Bruce Momjian <[email protected]> @ 2019-12-27 19:49 ` Bruce Momjian <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Bruce Momjian @ 2019-12-27 19:49 UTC (permalink / raw) To: Tuomas Leikola <[email protected]>; +Cc: [email protected] 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 <[email protected]> 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 <[email protected]> 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 + ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2019-12-27 19:49 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-09-04 13:12 uniqueness and null could benefit from a hint for dba PG Doc comments form <[email protected]> 2019-09-27 16:37 ` Bruce Momjian <[email protected]> 2019-10-23 11:35 ` Tuomas Leikola <[email protected]> 2019-10-24 14:31 ` Bruce Momjian <[email protected]> 2019-10-29 12:00 ` Tuomas Leikola <[email protected]> 2019-11-05 17:13 ` Bruce Momjian <[email protected]> 2019-12-27 19:49 ` Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox