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

Commit6456947

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 parent820b1d1 commit6456947

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
@@ -662,6 +662,15 @@ CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
662662
</programlisting>
663663
</para>
664664

665+
<para>
666+
Expression indexes also allow control over the scope of unique indexes.
667+
For example, this unique index prevents duplicate integer values from
668+
being stored in a <type>double precision</type>-typed column:
669+
<programlisting>
670+
CREATE UNIQUE INDEX test1_uniq_int ON tests ((floor(double_col)))
671+
</programlisting>
672+
</para>
673+
665674
<para>
666675
If we were to declare this index <literal>UNIQUE</>, it would prevent
667676
creation of rows whose <literal>col1</> values differ only in case,
@@ -901,6 +910,16 @@ CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
901910
This is a particularly efficient approach when there are few
902911
successful tests and many unsuccessful ones.
903912
</para>
913+
914+
<para>
915+
This index allows only one null in the indexed column by using a
916+
partial index clause to process only null column values, and using
917+
an expression index clause to index <literal>true</literal> instead
918+
of <literal>null</literal>:
919+
<programlisting>
920+
CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;
921+
</programlisting>
922+
</para>
904923
</example>
905924

906925
<para>

0 commit comments

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