<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>11.11. Indexes and Collations</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="indexes-opclass.html" title="11.10. Operator Classes and Operator Families" /><link rel="next" href="indexes-examine.html" title="11.12. Examining Index Usage" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">11.11. Indexes and Collations</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="indexes-opclass.html" title="11.10. Operator Classes and Operator Families">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="indexes.html" title="Chapter 11. Indexes">Up</a></td><th width="60%" align="center">Chapter 11. Indexes</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="indexes-examine.html" title="11.12. Examining Index Usage">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="INDEXES-COLLATIONS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">11.11. Indexes and Collations</h2></div></div></div><p>
An index can support only one collation per index column.
If multiple collations are of interest, multiple indexes may be needed.
</p><p>
Consider these statements:
</p><pre class="programlisting">
CREATE TABLE test1c (
id integer,
content varchar COLLATE "x"
);
CREATE INDEX test1c_content_index ON test1c (content);
</pre><p>
The index automatically uses the collation of the
underlying column. So a query of the form
</p><pre class="programlisting">
SELECT * FROM test1c WHERE content > <em class="replaceable"><code>constant</code></em>;
</pre><p>
could use the index, because the comparison will by default use the
collation of the column. However, this index cannot accelerate queries
that involve some other collation. So if queries of the form, say,
</p><pre class="programlisting">
SELECT * FROM test1c WHERE content > <em class="replaceable"><code>constant</code></em> COLLATE "y";
</pre><p>
are also of interest, an additional index could be created that supports
the <code class="literal">"y"</code> collation, like this:
</p><pre class="programlisting">
CREATE INDEX test1c_content_y_index ON test1c (content COLLATE "y");
</pre><p>
</p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="indexes-opclass.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="indexes.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="indexes-examine.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">11.10. Operator Classes and Operator Families </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 11.12. Examining Index Usage</td></tr></table></div></body></html>
