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

Commite6cf172

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 parent4666da1 commite6cf172

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
@@ -697,6 +697,15 @@ CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
697697
</programlisting>
698698
</para>
699699

700+
<para>
701+
Expression indexes also allow control over the scope of unique indexes.
702+
For example, this unique index prevents duplicate integer values from
703+
being stored in a <type>double precision</type>-typed column:
704+
<programlisting>
705+
CREATE UNIQUE INDEX test1_uniq_int ON tests ((floor(double_col)))
706+
</programlisting>
707+
</para>
708+
700709
<para>
701710
If we were to declare this index <literal>UNIQUE</literal>, it would prevent
702711
creation of rows whose <literal>col1</literal> values differ only in case,
@@ -936,6 +945,16 @@ CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
936945
This is a particularly efficient approach when there are few
937946
successful tests and many unsuccessful ones.
938947
</para>
948+
949+
<para>
950+
This index allows only one null in the indexed column by using a
951+
partial index clause to process only null column values, and using
952+
an expression index clause to index <literal>true</literal> instead
953+
of <literal>null</literal>:
954+
<programlisting>
955+
CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;
956+
</programlisting>
957+
</para>
939958
</example>
940959

941960
<para>

0 commit comments

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