Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
collation.html603 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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>24.2. Collation Support</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="locale.html" title="24.1. Locale Support" /><link rel="next" href="multibyte.html" title="24.3. Character Set Support" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">24.2. Collation Support</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="locale.html" title="24.1. Locale Support">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="charset.html" title="Chapter 24. Localization">Up</a></td><th width="60%" align="center">Chapter 24. Localization</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="multibyte.html" title="24.3. Character Set Support">Next</a></td></tr></table><hr /></div><div class="sect1" id="COLLATION"><div class="titlepage"><div><div><h2 class="title" style="clear: both">24.2. Collation Support <a href="#COLLATION" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="collation.html#COLLATION-CONCEPTS">24.2.1. Concepts</a></span></dt><dt><span class="sect2"><a href="collation.html#COLLATION-MANAGING">24.2.2. Managing Collations</a></span></dt><dt><span class="sect2"><a href="collation.html#ICU-CUSTOM-COLLATIONS">24.2.3. ICU Custom Collations</a></span></dt></dl></div><a id="id-1.6.11.4.2" class="indexterm"></a><p>3   The collation feature allows specifying the sort order and character4   classification behavior of data per-column, or even per-operation.5   This alleviates the restriction that the6   <code class="symbol">LC_COLLATE</code> and <code class="symbol">LC_CTYPE</code> settings7   of a database cannot be changed after its creation.8  </p><div class="sect2" id="COLLATION-CONCEPTS"><div class="titlepage"><div><div><h3 class="title">24.2.1. Concepts <a href="#COLLATION-CONCEPTS" class="id_link">#</a></h3></div></div></div><p>9    Conceptually, every expression of a collatable data type has a10    collation.  (The built-in collatable data types are11    <code class="type">text</code>, <code class="type">varchar</code>, and <code class="type">char</code>.12    User-defined base types can also be marked collatable, and of course13    a <a class="glossterm" href="glossary.html#GLOSSARY-DOMAIN"><em class="glossterm"><a class="glossterm" href="glossary.html#GLOSSARY-DOMAIN" title="Domain">domain</a></em></a> over a14    collatable data type is collatable.)  If the15    expression is a column reference, the collation of the expression is the16    defined collation of the column.  If the expression is a constant, the17    collation is the default collation of the data type of the18    constant.  The collation of a more complex expression is derived19    from the collations of its inputs, as described below.20   </p><p>21    The collation of an expression can be the <span class="quote">“<span class="quote">default</span>”</span>22    collation, which means the locale settings defined for the23    database.  It is also possible for an expression's collation to be24    indeterminate.  In such cases, ordering operations and other25    operations that need to know the collation will fail.26   </p><p>27    When the database system has to perform an ordering or a character28    classification, it uses the collation of the input expression.  This29    happens, for example, with <code class="literal">ORDER BY</code> clauses30    and function or operator calls such as <code class="literal">&lt;</code>.31    The collation to apply for an <code class="literal">ORDER BY</code> clause32    is simply the collation of the sort key.  The collation to apply for a33    function or operator call is derived from the arguments, as described34    below.  In addition to comparison operators, collations are taken into35    account by functions that convert between lower and upper case36    letters, such as <code class="function">lower</code>, <code class="function">upper</code>, and37    <code class="function">initcap</code>; by pattern matching operators; and by38    <code class="function">to_char</code> and related functions.39   </p><p>40    For a function or operator call, the collation that is derived by41    examining the argument collations is used at run time for performing42    the specified operation.  If the result of the function or operator43    call is of a collatable data type, the collation is also used at parse44    time as the defined collation of the function or operator expression,45    in case there is a surrounding expression that requires knowledge of46    its collation.47   </p><p>48    The <em class="firstterm">collation derivation</em> of an expression can be49    implicit or explicit.  This distinction affects how collations are50    combined when multiple different collations appear in an51    expression.  An explicit collation derivation occurs when a52    <code class="literal">COLLATE</code> clause is used; all other collation53    derivations are implicit.  When multiple collations need to be54    combined, for example in a function call, the following rules are55    used:56 57    </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>58       If any input expression has an explicit collation derivation, then59       all explicitly derived collations among the input expressions must be60       the same, otherwise an error is raised.  If any explicitly61       derived collation is present, that is the result of the62       collation combination.63      </p></li><li class="listitem"><p>64       Otherwise, all input expressions must have the same implicit65       collation derivation or the default collation.  If any non-default66       collation is present, that is the result of the collation combination.67       Otherwise, the result is the default collation.68      </p></li><li class="listitem"><p>69       If there are conflicting non-default implicit collations among the70       input expressions, then the combination is deemed to have indeterminate71       collation.  This is not an error condition unless the particular72       function being invoked requires knowledge of the collation it should73       apply.  If it does, an error will be raised at run-time.74      </p></li></ol></div><p>75 76    For example, consider this table definition:77</p><pre class="programlisting">78CREATE TABLE test1 (79    a text COLLATE "de_DE",80    b text COLLATE "es_ES",81    ...82);83</pre><p>84 85    Then in86</p><pre class="programlisting">87SELECT a &lt; 'foo' FROM test1;88</pre><p>89    the <code class="literal">&lt;</code> comparison is performed according to90    <code class="literal">de_DE</code> rules, because the expression combines an91    implicitly derived collation with the default collation.  But in92</p><pre class="programlisting">93SELECT a &lt; ('foo' COLLATE "fr_FR") FROM test1;94</pre><p>95    the comparison is performed using <code class="literal">fr_FR</code> rules,96    because the explicit collation derivation overrides the implicit one.97    Furthermore, given98</p><pre class="programlisting">99SELECT a &lt; b FROM test1;100</pre><p>101    the parser cannot determine which collation to apply, since the102    <code class="structfield">a</code> and <code class="structfield">b</code> columns have conflicting103    implicit collations.  Since the <code class="literal">&lt;</code> operator104    does need to know which collation to use, this will result in an105    error.  The error can be resolved by attaching an explicit collation106    specifier to either input expression, thus:107</p><pre class="programlisting">108SELECT a &lt; b COLLATE "de_DE" FROM test1;109</pre><p>110    or equivalently111</p><pre class="programlisting">112SELECT a COLLATE "de_DE" &lt; b FROM test1;113</pre><p>114    On the other hand, the structurally similar case115</p><pre class="programlisting">116SELECT a || b FROM test1;117</pre><p>118    does not result in an error, because the <code class="literal">||</code> operator119    does not care about collations: its result is the same regardless120    of the collation.121   </p><p>122    The collation assigned to a function or operator's combined input123    expressions is also considered to apply to the function or operator's124    result, if the function or operator delivers a result of a collatable125    data type.  So, in126</p><pre class="programlisting">127SELECT * FROM test1 ORDER BY a || 'foo';128</pre><p>129    the ordering will be done according to <code class="literal">de_DE</code> rules.130    But this query:131</p><pre class="programlisting">132SELECT * FROM test1 ORDER BY a || b;133</pre><p>134    results in an error, because even though the <code class="literal">||</code> operator135    doesn't need to know a collation, the <code class="literal">ORDER BY</code> clause does.136    As before, the conflict can be resolved with an explicit collation137    specifier:138</p><pre class="programlisting">139SELECT * FROM test1 ORDER BY a || b COLLATE "fr_FR";140</pre><p>141   </p></div><div class="sect2" id="COLLATION-MANAGING"><div class="titlepage"><div><div><h3 class="title">24.2.2. Managing Collations <a href="#COLLATION-MANAGING" class="id_link">#</a></h3></div></div></div><p>142    A collation is an SQL schema object that maps an SQL name to locales143    provided by libraries installed in the operating system.  A collation144    definition has a <em class="firstterm">provider</em> that specifies which145    library supplies the locale data.  One standard provider name146    is <code class="literal">libc</code>, which uses the locales provided by the147    operating system C library.  These are the locales used by most tools148    provided by the operating system.  Another provider149    is <code class="literal">icu</code>, which uses the external150    ICU<a id="id-1.6.11.4.5.2.4" class="indexterm"></a> library.  ICU locales can only be151    used if support for ICU was configured when PostgreSQL was built.152   </p><p>153    A collation object provided by <code class="literal">libc</code> maps to a154    combination of <code class="symbol">LC_COLLATE</code> and <code class="symbol">LC_CTYPE</code>155    settings, as accepted by the <code class="literal">setlocale()</code> system library call.  (As156    the name would suggest, the main purpose of a collation is to set157    <code class="symbol">LC_COLLATE</code>, which controls the sort order.  But158    it is rarely necessary in practice to have an159    <code class="symbol">LC_CTYPE</code> setting that is different from160    <code class="symbol">LC_COLLATE</code>, so it is more convenient to collect161    these under one concept than to create another infrastructure for162    setting <code class="symbol">LC_CTYPE</code> per expression.)  Also,163    a <code class="literal">libc</code> collation164    is tied to a character set encoding (see <a class="xref" href="multibyte.html" title="24.3. Character Set Support">Section 24.3</a>).165    The same collation name may exist for different encodings.166   </p><p>167    A collation object provided by <code class="literal">icu</code> maps to a named168    collator provided by the ICU library.  ICU does not support169    separate <span class="quote">“<span class="quote">collate</span>”</span> and <span class="quote">“<span class="quote">ctype</span>”</span> settings, so170    they are always the same.  Also, ICU collations are independent of the171    encoding, so there is always only one ICU collation of a given name in172    a database.173   </p><div class="sect3" id="COLLATION-MANAGING-STANDARD"><div class="titlepage"><div><div><h4 class="title">24.2.2.1. Standard Collations <a href="#COLLATION-MANAGING-STANDARD" class="id_link">#</a></h4></div></div></div><p>174    On all platforms, the collations named <code class="literal">default</code>,175    <code class="literal">C</code>, and <code class="literal">POSIX</code> are available.  Additional176    collations may be available depending on operating system support.177    The <code class="literal">default</code> collation selects the <code class="symbol">LC_COLLATE</code>178    and <code class="symbol">LC_CTYPE</code> values specified at database creation time.179    The <code class="literal">C</code> and <code class="literal">POSIX</code> collations both specify180    <span class="quote">“<span class="quote">traditional C</span>”</span> behavior, in which only the ASCII letters181    <span class="quote">“<span class="quote"><code class="literal">A</code></span>”</span> through <span class="quote">“<span class="quote"><code class="literal">Z</code></span>”</span>182    are treated as letters, and sorting is done strictly by character183    code byte values.184   </p><div class="note"><h3 class="title">Note</h3><p>185     The <code class="literal">C</code> and <code class="literal">POSIX</code> locales may behave186     differently depending on the database encoding.187    </p></div><p>188    Additionally, two SQL standard collation names are available:189 190    </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">unicode</code></span></dt><dd><p>191        This collation sorts using the Unicode Collation Algorithm with the192        Default Unicode Collation Element Table.  It is available in all193        encodings.  ICU support is required to use this collation.  (This194        collation has the same behavior as the ICU root locale; see <a class="xref" href="collation.html#COLLATION-MANAGING-PREDEFINED-ICU-UND-X-ICU"><code class="literal">und-x-icu</code> (for <span class="quote">“<span class="quote">undefined</span>”</span>)</a>.)195       </p></dd><dt><span class="term"><code class="literal">ucs_basic</code></span></dt><dd><p>196        This collation sorts by Unicode code point.  It is only available for197        encoding <code class="literal">UTF8</code>.  (This collation has the same198        behavior as the libc locale specification <code class="literal">C</code> in199        <code class="literal">UTF8</code> encoding.)200       </p></dd></dl></div><p>201   </p></div><div class="sect3" id="COLLATION-MANAGING-PREDEFINED"><div class="titlepage"><div><div><h4 class="title">24.2.2.2. Predefined Collations <a href="#COLLATION-MANAGING-PREDEFINED" class="id_link">#</a></h4></div></div></div><p>202    If the operating system provides support for using multiple locales203    within a single program (<code class="function">newlocale</code> and related functions),204    or if support for ICU is configured,205    then when a database cluster is initialized, <code class="command">initdb</code>206    populates the system catalog <code class="literal">pg_collation</code> with207    collations based on all the locales it finds in the operating208    system at the time.209   </p><p>210    To inspect the currently available locales, use the query <code class="literal">SELECT211    * FROM pg_collation</code>, or the command <code class="command">\dOS+</code>212    in <span class="application">psql</span>.213   </p><div class="sect4" id="COLLATION-MANAGING-PREDEFINED-LIBC"><div class="titlepage"><div><div><h5 class="title">24.2.2.2.1. libc Collations <a href="#COLLATION-MANAGING-PREDEFINED-LIBC" class="id_link">#</a></h5></div></div></div><p>214    For example, the operating system might215    provide a locale named <code class="literal">de_DE.utf8</code>.216    <code class="command">initdb</code> would then create a collation named217    <code class="literal">de_DE.utf8</code> for encoding <code class="literal">UTF8</code>218    that has both <code class="symbol">LC_COLLATE</code> and219    <code class="symbol">LC_CTYPE</code> set to <code class="literal">de_DE.utf8</code>.220    It will also create a collation with the <code class="literal">.utf8</code>221    tag stripped off the name.  So you could also use the collation222    under the name <code class="literal">de_DE</code>, which is less cumbersome223    to write and makes the name less encoding-dependent.  Note that,224    nevertheless, the initial set of collation names is225    platform-dependent.226   </p><p>227    The default set of collations provided by <code class="literal">libc</code> map228    directly to the locales installed in the operating system, which can be229    listed using the command <code class="literal">locale -a</code>.  In case230    a <code class="literal">libc</code> collation is needed that has different values231    for <code class="symbol">LC_COLLATE</code> and <code class="symbol">LC_CTYPE</code>, or if new232    locales are installed in the operating system after the database system233    was initialized, then a new collation may be created using234    the <a class="xref" href="sql-createcollation.html" title="CREATE COLLATION"><span class="refentrytitle">CREATE COLLATION</span></a> command.235    New operating system locales can also be imported en masse using236    the <a class="link" href="functions-admin.html#FUNCTIONS-ADMIN-COLLATION" title="Table 9.98. Collation Management Functions"><code class="function">pg_import_system_collations()</code></a> function.237   </p><p>238    Within any particular database, only collations that use that239    database's encoding are of interest.  Other entries in240    <code class="literal">pg_collation</code> are ignored.  Thus, a stripped collation241    name such as <code class="literal">de_DE</code> can be considered unique242    within a given database even though it would not be unique globally.243    Use of the stripped collation names is recommended, since it will244    make one fewer thing you need to change if you decide to change to245    another database encoding.  Note however that the <code class="literal">default</code>,246    <code class="literal">C</code>, and <code class="literal">POSIX</code> collations can be used regardless of247    the database encoding.248   </p><p>249    <span class="productname">PostgreSQL</span> considers distinct collation250    objects to be incompatible even when they have identical properties.251    Thus for example,252</p><pre class="programlisting">253SELECT a COLLATE "C" &lt; b COLLATE "POSIX" FROM test1;254</pre><p>255    will draw an error even though the <code class="literal">C</code> and <code class="literal">POSIX</code>256    collations have identical behaviors.  Mixing stripped and non-stripped257    collation names is therefore not recommended.258   </p></div><div class="sect4" id="COLLATION-MANAGING-PREDEFINED-ICU"><div class="titlepage"><div><div><h5 class="title">24.2.2.2.2. ICU Collations <a href="#COLLATION-MANAGING-PREDEFINED-ICU" class="id_link">#</a></h5></div></div></div><p>259    With ICU, it is not sensible to enumerate all possible locale names.  ICU260    uses a particular naming system for locales, but there are many more ways261    to name a locale than there are actually distinct locales.262    <code class="command">initdb</code> uses the ICU APIs to extract a set of distinct263    locales to populate the initial set of collations.  Collations provided by264    ICU are created in the SQL environment with names in BCP 47 language tag265    format, with a <span class="quote">“<span class="quote">private use</span>”</span>266    extension <code class="literal">-x-icu</code> appended, to distinguish them from267    libc locales.268   </p><p>269    Here are some example collations that might be created:270 271    </p><div class="variablelist"><dl class="variablelist"><dt id="COLLATION-MANAGING-PREDEFINED-ICU-DE-X-ICU"><span class="term"><code class="literal">de-x-icu</code></span> <a href="#COLLATION-MANAGING-PREDEFINED-ICU-DE-X-ICU" class="id_link">#</a></dt><dd><p>German collation, default variant</p></dd><dt id="COLLATION-MANAGING-PREDEFINED-ICU-DE-AT-X-ICU"><span class="term"><code class="literal">de-AT-x-icu</code></span> <a href="#COLLATION-MANAGING-PREDEFINED-ICU-DE-AT-X-ICU" class="id_link">#</a></dt><dd><p>German collation for Austria, default variant</p><p>272        (There are also, say, <code class="literal">de-DE-x-icu</code>273        or <code class="literal">de-CH-x-icu</code>, but as of this writing, they are274        equivalent to <code class="literal">de-x-icu</code>.)275       </p></dd><dt id="COLLATION-MANAGING-PREDEFINED-ICU-UND-X-ICU"><span class="term"><code class="literal">und-x-icu</code> (for <span class="quote">“<span class="quote">undefined</span>”</span>)</span> <a href="#COLLATION-MANAGING-PREDEFINED-ICU-UND-X-ICU" class="id_link">#</a></dt><dd><p>276        ICU <span class="quote">“<span class="quote">root</span>”</span> collation.  Use this to get a reasonable277        language-agnostic sort order.278       </p></dd></dl></div><p>279   </p><p>280    Some (less frequently used) encodings are not supported by ICU.  When the281    database encoding is one of these, ICU collation entries282    in <code class="literal">pg_collation</code> are ignored.  Attempting to use one283    will draw an error along the lines of <span class="quote">“<span class="quote">collation "de-x-icu" for284    encoding "WIN874" does not exist</span>”</span>.285   </p></div></div><div class="sect3" id="COLLATION-CREATE"><div class="titlepage"><div><div><h4 class="title">24.2.2.3. Creating New Collation Objects <a href="#COLLATION-CREATE" class="id_link">#</a></h4></div></div></div><p>286    If the standard and predefined collations are not sufficient, users can287    create their own collation objects using the SQL288    command <a class="xref" href="sql-createcollation.html" title="CREATE COLLATION"><span class="refentrytitle">CREATE COLLATION</span></a>.289   </p><p>290    The standard and predefined collations are in the291    schema <code class="literal">pg_catalog</code>, like all predefined objects.292    User-defined collations should be created in user schemas.  This also293    ensures that they are saved by <code class="command">pg_dump</code>.294   </p><div class="sect4" id="COLLATION-MANAGING-CREATE-LIBC"><div class="titlepage"><div><div><h5 class="title">24.2.2.3.1. libc Collations <a href="#COLLATION-MANAGING-CREATE-LIBC" class="id_link">#</a></h5></div></div></div><p>295     New libc collations can be created like this:296</p><pre class="programlisting">297CREATE COLLATION german (provider = libc, locale = 'de_DE');298</pre><p>299     The exact values that are acceptable for the <code class="literal">locale</code>300     clause in this command depend on the operating system.  On Unix-like301     systems, the command <code class="literal">locale -a</code> will show a list.302    </p><p>303     Since the predefined libc collations already include all collations304     defined in the operating system when the database instance is305     initialized, it is not often necessary to manually create new ones.306     Reasons might be if a different naming system is desired (in which case307     see also <a class="xref" href="collation.html#COLLATION-COPY" title="24.2.2.3.3. Copying Collations">Section 24.2.2.3.3</a>) or if the operating system has308     been upgraded to provide new locale definitions (in which case see309     also <a class="link" href="functions-admin.html#FUNCTIONS-ADMIN-COLLATION" title="Table 9.98. Collation Management Functions"><code class="function">pg_import_system_collations()</code></a>).310    </p></div><div class="sect4" id="COLLATION-MANAGING-CREATE-ICU"><div class="titlepage"><div><div><h5 class="title">24.2.2.3.2. ICU Collations <a href="#COLLATION-MANAGING-CREATE-ICU" class="id_link">#</a></h5></div></div></div><p>311     ICU collations can be created like:312 313</p><pre class="programlisting">314CREATE COLLATION german (provider = icu, locale = 'de-DE');315</pre><p>316 317     ICU locales are specified as a BCP 47 <a class="link" href="locale.html#ICU-LANGUAGE-TAG" title="24.1.5.3. Language Tag">Language Tag</a>, but can also accept most318     libc-style locale names. If possible, libc-style locale names are319     transformed into language tags.320    </p><p>321     New ICU collations can customize collation behavior extensively by322     including collation attributes in the language tag. See <a class="xref" href="collation.html#ICU-CUSTOM-COLLATIONS" title="24.2.3. ICU Custom Collations">Section 24.2.3</a> for details and examples.323    </p></div><div class="sect4" id="COLLATION-COPY"><div class="titlepage"><div><div><h5 class="title">24.2.2.3.3. Copying Collations <a href="#COLLATION-COPY" class="id_link">#</a></h5></div></div></div><p>324    The command <a class="xref" href="sql-createcollation.html" title="CREATE COLLATION"><span class="refentrytitle">CREATE COLLATION</span></a> can also be used to325    create a new collation from an existing collation, which can be useful to326    be able to use operating-system-independent collation names in327    applications, create compatibility names, or use an ICU-provided collation328    under a more readable name.  For example:329</p><pre class="programlisting">330CREATE COLLATION german FROM "de_DE";331CREATE COLLATION french FROM "fr-x-icu";332</pre><p>333   </p></div></div><div class="sect3" id="COLLATION-NONDETERMINISTIC"><div class="titlepage"><div><div><h4 class="title">24.2.2.4. Nondeterministic Collations <a href="#COLLATION-NONDETERMINISTIC" class="id_link">#</a></h4></div></div></div><p>334     A collation is either <em class="firstterm">deterministic</em> or335     <em class="firstterm">nondeterministic</em>.  A deterministic collation uses336     deterministic comparisons, which means that it considers strings to be337     equal only if they consist of the same byte sequence.  Nondeterministic338     comparison may determine strings to be equal even if they consist of339     different bytes.  Typical situations include case-insensitive comparison,340     accent-insensitive comparison, as well as comparison of strings in341     different Unicode normal forms.  It is up to the collation provider to342     actually implement such insensitive comparisons; the deterministic flag343     only determines whether ties are to be broken using bytewise comparison.344     See also <a class="ulink" href="https://www.unicode.org/reports/tr10" target="_top">Unicode Technical345     Standard 10</a> for more information on the terminology.346    </p><p>347     To create a nondeterministic collation, specify the property348     <code class="literal">deterministic = false</code> to <code class="command">CREATE349     COLLATION</code>, for example:350</p><pre class="programlisting">351CREATE COLLATION ndcoll (provider = icu, locale = 'und', deterministic = false);352</pre><p>353     This example would use the standard Unicode collation in a354     nondeterministic way.  In particular, this would allow strings in355     different normal forms to be compared correctly.  More interesting356     examples make use of the ICU customization facilities explained above.357     For example:358</p><pre class="programlisting">359CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);360CREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false);361</pre><p>362    </p><p>363     All standard and predefined collations are deterministic, all364     user-defined collations are deterministic by default.  While365     nondeterministic collations give a more <span class="quote">“<span class="quote">correct</span>”</span> behavior,366     especially when considering the full power of Unicode and its many367     special cases, they also have some drawbacks.  Foremost, their use leads368     to a performance penalty.  Note, in particular, that B-tree cannot use369     deduplication with indexes that use a nondeterministic collation.  Also,370     certain operations are not possible with nondeterministic collations,371     such as pattern matching operations.  Therefore, they should be used372     only in cases where they are specifically wanted.373    </p><div class="tip"><h3 class="title">Tip</h3><p>374      To deal with text in different Unicode normalization forms, it is also375      an option to use the functions/expressions376      <code class="function">normalize</code> and <code class="literal">is normalized</code> to377      preprocess or check the strings, instead of using nondeterministic378      collations.  There are different trade-offs for each approach.379     </p></div></div></div><div class="sect2" id="ICU-CUSTOM-COLLATIONS"><div class="titlepage"><div><div><h3 class="title">24.2.3. ICU Custom Collations <a href="#ICU-CUSTOM-COLLATIONS" class="id_link">#</a></h3></div></div></div><p>380    ICU allows extensive control over collation behavior by defining new381    collations with collation settings as a part of the language tag. These382    settings can modify the collation order to suit a variety of needs. For383    instance:384 385</p><pre class="programlisting">386-- ignore differences in accents and case387CREATE COLLATION ignore_accent_case (provider = icu, deterministic = false, locale = 'und-u-ks-level1');388SELECT 'Å' = 'A' COLLATE ignore_accent_case; -- true389SELECT 'z' = 'Z' COLLATE ignore_accent_case; -- true390 391-- upper case letters sort before lower case.392CREATE COLLATION upper_first (provider = icu, locale = 'und-u-kf-upper');393SELECT 'B' &lt; 'b' COLLATE upper_first; -- true394 395-- treat digits numerically and ignore punctuation396CREATE COLLATION num_ignore_punct (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-kn');397SELECT 'id-45' &lt; 'id-123' COLLATE num_ignore_punct; -- true398SELECT 'w;x*y-z' = 'wxyz' COLLATE num_ignore_punct; -- true399</pre><p>400 401    Many of the available options are described in <a class="xref" href="collation.html#ICU-COLLATION-SETTINGS" title="24.2.3.2. Collation Settings for an ICU Locale">Section 24.2.3.2</a>, or see <a class="xref" href="collation.html#ICU-EXTERNAL-REFERENCES" title="24.2.3.5. External References for ICU">Section 24.2.3.5</a> for more details.402   </p><div class="sect3" id="ICU-COLLATION-COMPARISON-LEVELS"><div class="titlepage"><div><div><h4 class="title">24.2.3.1. ICU Comparison Levels <a href="#ICU-COLLATION-COMPARISON-LEVELS" class="id_link">#</a></h4></div></div></div><p>403     Comparison of two strings (collation) in ICU is determined by a404     multi-level process, where textual features are grouped into405     "levels". Treatment of each level is controlled by the <a class="link" href="collation.html#ICU-COLLATION-SETTINGS-TABLE" title="Table 24.2. ICU Collation Settings">collation settings</a>. Higher406     levels correspond to finer textual features.407    </p><p>408     <a class="xref" href="collation.html#ICU-COLLATION-LEVELS" title="Table 24.1. ICU Collation Levels">Table 24.1</a> shows which textual feature409     differences are considered significant when determining equality at the410     given level. The Unicode character <code class="literal">U+2063</code> is an411     invisible separator, and as seen in the table, is ignored for at all412     levels of comparison less than <code class="literal">identic</code>.413    </p><div class="table" id="ICU-COLLATION-LEVELS"><p class="title"><strong>Table 24.1. ICU Collation Levels</strong></p><div class="table-contents"><table class="table" summary="ICU Collation Levels" border="1"><colgroup><col class="col1" /><col class="col2" /><col class="col3" /><col class="col4" /><col class="col5" /><col class="col6" /><col class="col7" /><col class="col8" /></colgroup><thead><tr><th>Level</th><th>Description</th><th><code class="literal">'f' = 'f'</code></th><th><code class="literal">'ab' = U&amp;'a\2063b'</code></th><th><code class="literal">'x-y' = 'x_y'</code></th><th><code class="literal">'g' = 'G'</code></th><th><code class="literal">'n' = 'ñ'</code></th><th><code class="literal">'y' = 'z'</code></th></tr></thead><tbody><tr><td>level1</td><td>Base Character</td><td><code class="literal">true</code></td><td><code class="literal">true</code></td><td><code class="literal">true</code></td><td><code class="literal">true</code></td><td><code class="literal">true</code></td><td><code class="literal">false</code></td></tr><tr><td>level2</td><td>Accents</td><td><code class="literal">true</code></td><td><code class="literal">true</code></td><td><code class="literal">true</code></td><td><code class="literal">true</code></td><td><code class="literal">false</code></td><td><code class="literal">false</code></td></tr><tr><td>level3</td><td>Case/Variants</td><td><code class="literal">true</code></td><td><code class="literal">true</code></td><td><code class="literal">true</code></td><td><code class="literal">false</code></td><td><code class="literal">false</code></td><td><code class="literal">false</code></td></tr><tr><td>level4</td><td>Punctuation</td><td><code class="literal">true</code></td><td><code class="literal">true</code></td><td><code class="literal">false</code></td><td><code class="literal">false</code></td><td><code class="literal">false</code></td><td><code class="literal">false</code></td></tr><tr><td>identic</td><td>All</td><td><code class="literal">true</code></td><td><code class="literal">false</code></td><td><code class="literal">false</code></td><td><code class="literal">false</code></td><td><code class="literal">false</code></td><td><code class="literal">false</code></td></tr></tbody></table></div></div><br class="table-break" /><p>414     At every level, even with full normalization off, basic normalization is415     performed. For example, <code class="literal">'á'</code> may be composed of the416     code points <code class="literal">U&amp;'\0061\0301'</code> or the single code417     point <code class="literal">U&amp;'\00E1'</code>, and those sequences will be418     considered equal even at the <code class="literal">identic</code> level. To treat419     any difference in code point representation as distinct, use a collation420     created with <code class="symbol">deterministic</code> set to421     <code class="literal">true</code>.422    </p><div class="sect4" id="ICU-COLLATION-LEVEL-EXAMPLES"><div class="titlepage"><div><div><h5 class="title">24.2.3.1.1. Collation Level Examples <a href="#ICU-COLLATION-LEVEL-EXAMPLES" class="id_link">#</a></h5></div></div></div><pre class="programlisting">423CREATE COLLATION level3 (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-ks-level3');424CREATE COLLATION level4 (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-ks-level4');425CREATE COLLATION identic (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-ks-identic');426 427-- invisible separator ignored at all levels except identic428SELECT 'ab' = U&amp;'a\2063b' COLLATE level4; -- true429SELECT 'ab' = U&amp;'a\2063b' COLLATE identic; -- false430 431-- punctuation ignored at level3 but not at level 4432SELECT 'x-y' = 'x_y' COLLATE level3; -- true433SELECT 'x-y' = 'x_y' COLLATE level4; -- false434</pre></div></div><div class="sect3" id="ICU-COLLATION-SETTINGS"><div class="titlepage"><div><div><h4 class="title">24.2.3.2. Collation Settings for an ICU Locale <a href="#ICU-COLLATION-SETTINGS" class="id_link">#</a></h4></div></div></div><p>435     <a class="xref" href="collation.html#ICU-COLLATION-SETTINGS-TABLE" title="Table 24.2. ICU Collation Settings">Table 24.2</a> shows the available436     collation settings, which can be used as part of a language tag to437     customize a collation.438    </p><div class="table" id="ICU-COLLATION-SETTINGS-TABLE"><p class="title"><strong>Table 24.2. ICU Collation Settings</strong></p><div class="table-contents"><table class="table" summary="ICU Collation Settings" border="1"><colgroup><col class="col1" /><col class="col2" /><col class="col3" /><col class="col4" /></colgroup><thead><tr><th>Key</th><th>Values</th><th>Default</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">co</code></td><td><code class="literal">emoji</code>, <code class="literal">phonebk</code>, <code class="literal">standard</code>, <em class="replaceable"><code>...</code></em></td><td><code class="literal">standard</code></td><td>439          Collation type. See <a class="xref" href="collation.html#ICU-EXTERNAL-REFERENCES" title="24.2.3.5. External References for ICU">Section 24.2.3.5</a> for additional options and details.440         </td></tr><tr><td><code class="literal">ka</code></td><td><code class="literal">noignore</code>, <code class="literal">shifted</code></td><td><code class="literal">noignore</code></td><td>441          If set to <code class="literal">shifted</code>, causes some characters442          (e.g. punctuation or space) to be ignored in comparison. Key443          <code class="literal">ks</code> must be set to <code class="literal">level3</code> or444          lower to take effect. Set key <code class="literal">kv</code> to control which445          character classes are ignored.446         </td></tr><tr><td><code class="literal">kb</code></td><td><code class="literal">true</code>, <code class="literal">false</code></td><td><code class="literal">false</code></td><td>447          Backwards comparison for the level 2 differences. For example,448          locale <code class="literal">und-u-kb</code> sorts <code class="literal">'àe'</code>449          before <code class="literal">'aé'</code>.450         </td></tr><tr><td><code class="literal">kc</code></td><td><code class="literal">true</code>, <code class="literal">false</code></td><td><code class="literal">false</code></td><td>451          <p>452           Separates case into a "level 2.5" that falls between accents and453           other level 3 features.454          </p>455          <p>456           If set to <code class="literal">true</code> and <code class="literal">ks</code> is set457           to <code class="literal">level1</code>, will ignore accents but take case458           into account.459          </p>460         </td></tr><tr><td><code class="literal">kf</code></td><td>461          <code class="literal">upper</code>, <code class="literal">lower</code>,462          <code class="literal">false</code>463         </td><td><code class="literal">false</code></td><td>464          If set to <code class="literal">upper</code>, upper case sorts before lower465          case. If set to <code class="literal">lower</code>, lower case sorts before466          upper case. If set to <code class="literal">false</code>, the sort depends on467          the rules of the locale.468         </td></tr><tr><td><code class="literal">kn</code></td><td><code class="literal">true</code>, <code class="literal">false</code></td><td><code class="literal">false</code></td><td>469          If set to <code class="literal">true</code>, numbers within a string are470          treated as a single numeric value rather than a sequence of471          digits. For example, <code class="literal">'id-45'</code> sorts before472          <code class="literal">'id-123'</code>.473         </td></tr><tr><td><code class="literal">kk</code></td><td><code class="literal">true</code>, <code class="literal">false</code></td><td><code class="literal">false</code></td><td>474          <p>475           Enable full normalization; may affect performance. Basic476           normalization is performed even when set to477           <code class="literal">false</code>. Locales for languages that require full478           normalization typically enable it by default.479          </p>480          <p>481           Full normalization is important in some cases, such as when482           multiple accents are applied to a single character. For example,483           the code point sequences <code class="literal">U&amp;'\0065\0323\0302'</code>484           and <code class="literal">U&amp;'\0065\0302\0323'</code> represent485           an <code class="literal">e</code> with circumflex and dot-below accents486           applied in different orders. With full normalization487           on, these code point sequences are treated as equal; otherwise they488           are unequal.489          </p>490         </td></tr><tr><td><code class="literal">kr</code></td><td>491          <code class="literal">space</code>, <code class="literal">punct</code>,492          <code class="literal">symbol</code>, <code class="literal">currency</code>,493          <code class="literal">digit</code>, <em class="replaceable"><code>script-id</code></em>494         </td><td> </td><td>495          <p>496           Set to one or more of the valid values, or any BCP 47497           <em class="replaceable"><code>script-id</code></em>, e.g. <code class="literal">latn</code>498           ("Latin") or <code class="literal">grek</code> ("Greek"). Multiple values are499           separated by "<code class="literal">-</code>".500          </p>501          <p>502           Redefines the ordering of classes of characters; those characters503           belonging to a class earlier in the list sort before characters504           belonging to a class later in the list. For instance, the value505           <code class="literal">digit-currency-space</code> (as part of a language tag506           like <code class="literal">und-u-kr-digit-currency-space</code>) sorts507           punctuation before digits and spaces.508          </p>509         </td></tr><tr><td><code class="literal">ks</code></td><td><code class="literal">level1</code>, <code class="literal">level2</code>, <code class="literal">level3</code>, <code class="literal">level4</code>, <code class="literal">identic</code></td><td><code class="literal">level3</code></td><td>510          Sensitivity (or "strength") when determining equality, with511          <code class="literal">level1</code> the least sensitive to differences and512          <code class="literal">identic</code> the most sensitive to differences. See513          <a class="xref" href="collation.html#ICU-COLLATION-LEVELS" title="Table 24.1. ICU Collation Levels">Table 24.1</a> for details.514         </td></tr><tr><td><code class="literal">kv</code></td><td>515          <code class="literal">space</code>, <code class="literal">punct</code>,516          <code class="literal">symbol</code>, <code class="literal">currency</code>517         </td><td><code class="literal">punct</code></td><td>518          Classes of characters ignored during comparison at level 3. Setting519          to a later value includes earlier values;520          e.g. <code class="literal">symbol</code> also includes521          <code class="literal">punct</code> and <code class="literal">space</code> in the522          characters to be ignored. Key <code class="literal">ka</code> must be set to523          <code class="literal">shifted</code> and key <code class="literal">ks</code> must be set524          to <code class="literal">level3</code> or lower to take effect.525         </td></tr></tbody></table></div></div><br class="table-break" /><p>526     Defaults may depend on locale. The above table is not meant to be527     complete. See <a class="xref" href="collation.html#ICU-EXTERNAL-REFERENCES" title="24.2.3.5. External References for ICU">Section 24.2.3.5</a> for additional528     options and details.529    </p><div class="note"><h3 class="title">Note</h3><p>530      For many collation settings, you must create the collation with531      <code class="option">deterministic</code> set to <code class="literal">false</code> for the532      setting to have the desired effect (see <a class="xref" href="collation.html#COLLATION-NONDETERMINISTIC" title="24.2.2.4. Nondeterministic Collations">Section 24.2.2.4</a>). Additionally, some settings533      only take effect when the key <code class="literal">ka</code> is set to534      <code class="literal">shifted</code> (see <a class="xref" href="collation.html#ICU-COLLATION-SETTINGS-TABLE" title="Table 24.2. ICU Collation Settings">Table 24.2</a>).535     </p></div></div><div class="sect3" id="ICU-LOCALE-EXAMPLES"><div class="titlepage"><div><div><h4 class="title">24.2.3.3. Collation Settings Examples <a href="#ICU-LOCALE-EXAMPLES" class="id_link">#</a></h4></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="COLLATION-MANAGING-CREATE-ICU-DE-U-CO-PHONEBK-X-ICU"><span class="term"><code class="literal">CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de-u-co-phonebk');</code></span> <a href="#COLLATION-MANAGING-CREATE-ICU-DE-U-CO-PHONEBK-X-ICU" class="id_link">#</a></dt><dd><p>German collation with phone book collation type</p></dd><dt id="COLLATION-MANAGING-CREATE-ICU-UND-U-CO-EMOJI-X-ICU"><span class="term"><code class="literal">CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = 'und-u-co-emoji');</code></span> <a href="#COLLATION-MANAGING-CREATE-ICU-UND-U-CO-EMOJI-X-ICU" class="id_link">#</a></dt><dd><p>536         Root collation with Emoji collation type, per Unicode Technical Standard #51537        </p></dd><dt id="COLLATION-MANAGING-CREATE-ICU-EN-U-KR-GREK-LATN"><span class="term"><code class="literal">CREATE COLLATION latinlast (provider = icu, locale = 'en-u-kr-grek-latn');</code></span> <a href="#COLLATION-MANAGING-CREATE-ICU-EN-U-KR-GREK-LATN" class="id_link">#</a></dt><dd><p>538         Sort Greek letters before Latin ones.  (The default is Latin before Greek.)539        </p></dd><dt id="COLLATION-MANAGING-CREATE-ICU-EN-U-KF-UPPER"><span class="term"><code class="literal">CREATE COLLATION upperfirst (provider = icu, locale = 'en-u-kf-upper');</code></span> <a href="#COLLATION-MANAGING-CREATE-ICU-EN-U-KF-UPPER" class="id_link">#</a></dt><dd><p>540         Sort upper-case letters before lower-case letters.  (The default is541         lower-case letters first.)542        </p></dd><dt id="COLLATION-MANAGING-CREATE-ICU-EN-U-KF-UPPER-KR-GREK-LATN"><span class="term"><code class="literal">CREATE COLLATION special (provider = icu, locale = 'en-u-kf-upper-kr-grek-latn');</code></span> <a href="#COLLATION-MANAGING-CREATE-ICU-EN-U-KF-UPPER-KR-GREK-LATN" class="id_link">#</a></dt><dd><p>543         Combines both of the above options.544        </p></dd></dl></div></div><div class="sect3" id="ICU-TAILORING-RULES"><div class="titlepage"><div><div><h4 class="title">24.2.3.4. ICU Tailoring Rules <a href="#ICU-TAILORING-RULES" class="id_link">#</a></h4></div></div></div><p>545     If the options provided by the collation settings shown above are not546     sufficient, the order of collation elements can be changed with tailoring547     rules, whose syntax is detailed at <a class="ulink" href="https://unicode-org.github.io/icu/userguide/collation/customization/" target="_top">https://unicode-org.github.io/icu/userguide/collation/customization/</a>.548    </p><p>549     This small example creates a collation based on the root locale with a550     tailoring rule:551</p><pre class="programlisting">552CREATE COLLATION custom (provider = icu, locale = 'und', rules = '&amp;V &lt;&lt; w &lt;&lt;&lt; W');553</pre><p>554     With this rule, the letter <span class="quote">“<span class="quote">W</span>”</span> is sorted after555     <span class="quote">“<span class="quote">V</span>”</span>, but is treated as a secondary difference similar to an556     accent.  Rules like this are contained in the locale definitions of some557     languages.  (Of course, if a locale definition already contains the558     desired rules, then they don't need to be specified again explicitly.)559    </p><p>560     Here is a more complex example.  The following statement sets up a561     collation named <code class="literal">ebcdic</code> with rules to sort US-ASCII562     characters in the order of the EBCDIC encoding.563 564</p><pre class="programlisting">565CREATE COLLATION ebcdic (provider = icu, locale = 'und',566rules = $$567&amp; ' ' &lt; '.' &lt; '&lt;' &lt; '(' &lt; '+' &lt; \|568&lt; '&amp;' &lt; '!' &lt; '$' &lt; '*' &lt; ')' &lt; ';'569&lt; '-' &lt; '/' &lt; ',' &lt; '%' &lt; '_' &lt; '&gt;' &lt; '?'570&lt; '`' &lt; ':' &lt; '#' &lt; '@' &lt; \' &lt; '=' &lt; '"'571&lt;*a-r &lt; '~' &lt;*s-z &lt; '^' &lt; '[' &lt; ']'572&lt; '{' &lt;*A-I &lt; '}' &lt;*J-R &lt; '\' &lt;*S-Z &lt;*0-9573$$);574 575SELECT c576FROM (VALUES ('a'), ('b'), ('A'), ('B'), ('1'), ('2'), ('!'), ('^')) AS x(c)577ORDER BY c COLLATE ebcdic;578 c579---580 !581 a582 b583 ^584 A585 B586 1587 2588</pre><p>589    </p></div><div class="sect3" id="ICU-EXTERNAL-REFERENCES"><div class="titlepage"><div><div><h4 class="title">24.2.3.5. External References for ICU <a href="#ICU-EXTERNAL-REFERENCES" class="id_link">#</a></h4></div></div></div><p>590     This section (<a class="xref" href="collation.html#ICU-CUSTOM-COLLATIONS" title="24.2.3. ICU Custom Collations">Section 24.2.3</a>) is only a brief591     overview of ICU behavior and language tags. Refer to the following592     documents for technical details, additional options, and new behavior:593    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>594       <a class="ulink" href="https://www.unicode.org/reports/tr35/tr35-collation.html" target="_top">Unicode Technical Standard #35</a>595      </p></li><li class="listitem"><p>596       <a class="ulink" href="https://www.rfc-editor.org/info/bcp47" target="_top">BCP 47</a>597      </p></li><li class="listitem"><p>598       <a class="ulink" href="https://github.com/unicode-org/cldr/blob/master/common/bcp47/collation.xml" target="_top">CLDR repository</a>599      </p></li><li class="listitem"><p>600       <a class="ulink" href="https://unicode-org.github.io/icu/userguide/locale/" target="_top">https://unicode-org.github.io/icu/userguide/locale/</a>601      </p></li><li class="listitem"><p>602       <a class="ulink" href="https://unicode-org.github.io/icu/userguide/collation/" target="_top">https://unicode-org.github.io/icu/userguide/collation/</a>603      </p></li></ul></div></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="locale.html" title="24.1. Locale Support">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="charset.html" title="Chapter 24. Localization">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="multibyte.html" title="24.3. Character Set Support">Next</a></td></tr><tr><td width="40%" align="left" valign="top">24.1. Locale Support </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 24.3. Character Set Support</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai