Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita9760d0

Browse files
committed
doc: add examples of creative use of unique expression indexes
Unique expression indexes can constrain data in creative ways, so showtwo examples.Reported-by: Tuomas LeikolaDiscussion:https://postgr.es/m/156760275564.1127.12321702656456074572@wrigleys.postgresql.orgBackpatch-through: 9.4
1 parent650692a commita9760d0

File tree

1 file changed

+19
-0
lines changed

1 file changed

+19
-0
lines changed

‎doc/src/sgml/indices.sgml

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -705,6 +705,15 @@ CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
705705
</programlisting>
706706
</para>
707707

708+
<para>
709+
Expression indexes also allow control over the scope of unique indexes.
710+
For example, this unique index prevents duplicate integer values from
711+
being stored in a <type>double precision</type>-typed column:
712+
<programlisting>
713+
CREATE UNIQUE INDEX test1_uniq_int ON tests ((floor(double_col)))
714+
</programlisting>
715+
</para>
716+
708717
<para>
709718
If we were to declare this index <literal>UNIQUE</literal>, it would prevent
710719
creation of rows whose <literal>col1</literal> values differ only in case,
@@ -946,6 +955,16 @@ CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
946955
This is a particularly efficient approach when there are few
947956
successful tests and many unsuccessful ones.
948957
</para>
958+
959+
<para>
960+
This index allows only one null in the indexed column by using a
961+
partial index clause to process only null column values, and using
962+
an expression index clause to index <literal>true</literal> instead
963+
of <literal>null</literal>:
964+
<programlisting>
965+
CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;
966+
</programlisting>
967+
</para>
949968
</example>
950969

951970
<para>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp