codekingpro/portable-devtools
115k
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"><</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 < 'foo' FROM test1;88</pre><p>89 the <code class="literal"><</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 < ('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 < 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"><</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 < b COLLATE "de_DE" FROM test1;109</pre><p>110 or equivalently111</p><pre class="programlisting">112SELECT a COLLATE "de_DE" < 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" < 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' < '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' < '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&'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&'\0061\0301'</code> or the single code417 point <code class="literal">U&'\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&'a\2063b' COLLATE level4; -- true429SELECT 'ab' = U&'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&'\0065\0323\0302'</code>484 and <code class="literal">U&'\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 = '&V << w <<< 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& ' ' < '.' < '<' < '(' < '+' < \|568< '&' < '!' < '$' < '*' < ')' < ';'569< '-' < '/' < ',' < '%' < '_' < '>' < '?'570< '`' < ':' < '#' < '@' < \' < '=' < '"'571<*a-r < '~' <*s-z < '^' < '[' < ']'572< '{' <*A-I < '}' <*J-R < '\' <*S-Z <*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>