On Tue, Oct 29, 2019 at 02:00:38PM +0200, Tuomas Leikola wrote: > On Thu, Oct 24, 2019 at 5:31 PM Bruce Momjian <br...@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 <br...@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 +
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>