Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
locale.html348 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.1. Locale 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="charset.html" title="Chapter 24. Localization" /><link rel="next" href="collation.html" title="24.2. Collation 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.1. Locale Support</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="charset.html" title="Chapter 24. Localization">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="collation.html" title="24.2. Collation Support">Next</a></td></tr></table><hr /></div><div class="sect1" id="LOCALE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">24.1. Locale Support <a href="#LOCALE" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="locale.html#LOCALE-OVERVIEW">24.1.1. Overview</a></span></dt><dt><span class="sect2"><a href="locale.html#LOCALE-BEHAVIOR">24.1.2. Behavior</a></span></dt><dt><span class="sect2"><a href="locale.html#LOCALE-SELECTING-LOCALES">24.1.3. Selecting Locales</a></span></dt><dt><span class="sect2"><a href="locale.html#LOCALE-PROVIDERS">24.1.4. Locale Providers</a></span></dt><dt><span class="sect2"><a href="locale.html#ICU-LOCALES">24.1.5. ICU Locales</a></span></dt><dt><span class="sect2"><a href="locale.html#LOCALE-PROBLEMS">24.1.6. Problems</a></span></dt></dl></div><a id="id-1.6.11.3.2" class="indexterm"></a><p>3   <em class="firstterm">Locale</em> support refers to an application respecting4   cultural preferences regarding alphabets, sorting, number5   formatting, etc.  <span class="productname">PostgreSQL</span> uses the standard ISO6   C and <acronym class="acronym">POSIX</acronym> locale facilities provided by the server operating7   system.  For additional information refer to the documentation of your8   system.9  </p><div class="sect2" id="LOCALE-OVERVIEW"><div class="titlepage"><div><div><h3 class="title">24.1.1. Overview <a href="#LOCALE-OVERVIEW" class="id_link">#</a></h3></div></div></div><p>10    Locale support is automatically initialized when a database11    cluster is created using <code class="command">initdb</code>.12    <code class="command">initdb</code> will initialize the database cluster13    with the locale setting of its execution environment by default,14    so if your system is already set to use the locale that you want15    in your database cluster then there is nothing else you need to16    do.  If you want to use a different locale (or you are not sure17    which locale your system is set to), you can instruct18    <code class="command">initdb</code> exactly which locale to use by19    specifying the <code class="option">--locale</code> option. For example:20</p><pre class="screen">21initdb --locale=sv_SE22</pre><p>23   </p><p>24    This example for Unix systems sets the locale to Swedish25    (<code class="literal">sv</code>) as spoken26    in Sweden (<code class="literal">SE</code>).  Other possibilities might include27    <code class="literal">en_US</code> (U.S. English) and <code class="literal">fr_CA</code> (French28    Canadian).  If more than one character set can be used for a29    locale then the specifications can take the form30    <em class="replaceable"><code>language_territory.codeset</code></em>.  For example,31    <code class="literal">fr_BE.UTF-8</code> represents the French language (fr) as32    spoken in Belgium (BE), with a <acronym class="acronym">UTF-8</acronym> character set33    encoding.34   </p><p>35    What locales are available on your36    system under what names depends on what was provided by the operating37    system vendor and what was installed.  On most Unix systems, the command38    <code class="literal">locale -a</code> will provide a list of available locales.39    Windows uses more verbose locale names, such as <code class="literal">German_Germany</code>40    or <code class="literal">Swedish_Sweden.1252</code>, but the principles are the same.41   </p><p>42    Occasionally it is useful to mix rules from several locales, e.g.,43    use English collation rules but Spanish messages.  To support that, a44    set of locale subcategories exist that control only certain45    aspects of the localization rules:46 47    </p><div class="informaltable"><table class="informaltable" border="1"><colgroup><col class="col1" /><col class="col2" /></colgroup><tbody><tr><td><code class="envar">LC_COLLATE</code></td><td>String sort order</td></tr><tr><td><code class="envar">LC_CTYPE</code></td><td>Character classification (What is a letter? Its upper-case equivalent?)</td></tr><tr><td><code class="envar">LC_MESSAGES</code></td><td>Language of messages</td></tr><tr><td><code class="envar">LC_MONETARY</code></td><td>Formatting of currency amounts</td></tr><tr><td><code class="envar">LC_NUMERIC</code></td><td>Formatting of numbers</td></tr><tr><td><code class="envar">LC_TIME</code></td><td>Formatting of dates and times</td></tr></tbody></table></div><p>48 49    The category names translate into names of50    <code class="command">initdb</code> options to override the locale choice51    for a specific category.  For instance, to set the locale to52    French Canadian, but use U.S. rules for formatting currency, use53    <code class="literal">initdb --locale=fr_CA --lc-monetary=en_US</code>.54   </p><p>55    If you want the system to behave as if it had no locale support,56    use the special locale name <code class="literal">C</code>, or equivalently57    <code class="literal">POSIX</code>.58   </p><p>59    Some locale categories must have their values60    fixed when the database is created.  You can use different settings61    for different databases, but once a database is created, you cannot62    change them for that database anymore. <code class="literal">LC_COLLATE</code>63    and <code class="literal">LC_CTYPE</code> are these categories.  They affect64    the sort order of indexes, so they must be kept fixed, or indexes on65    text columns would become corrupt.66    (But you can alleviate this restriction using collations, as discussed67    in <a class="xref" href="collation.html" title="24.2. Collation Support">Section 24.2</a>.)68    The default values for these69    categories are determined when <code class="command">initdb</code> is run, and70    those values are used when new databases are created, unless71    specified otherwise in the <code class="command">CREATE DATABASE</code> command.72   </p><p>73    The other locale categories can be changed whenever desired74    by setting the server configuration parameters75    that have the same name as the locale categories (see <a class="xref" href="runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT" title="20.11.2. Locale and Formatting">Section 20.11.2</a> for details).  The values76    that are chosen by <code class="command">initdb</code> are actually only written77    into the configuration file <code class="filename">postgresql.conf</code> to78    serve as defaults when the server is started.  If you remove these79    assignments from <code class="filename">postgresql.conf</code> then the80    server will inherit the settings from its execution environment.81   </p><p>82    Note that the locale behavior of the server is determined by the83    environment variables seen by the server, not by the environment84    of any client.  Therefore, be careful to configure the correct locale settings85    before starting the server.  A consequence of this is that if86    client and server are set up in different locales, messages might87    appear in different languages depending on where they originated.88   </p><div class="note"><h3 class="title">Note</h3><p>89     When we speak of inheriting the locale from the execution90     environment, this means the following on most operating systems:91     For a given locale category, say the collation, the following92     environment variables are consulted in this order until one is93     found to be set: <code class="envar">LC_ALL</code>, <code class="envar">LC_COLLATE</code>94     (or the variable corresponding to the respective category),95     <code class="envar">LANG</code>.  If none of these environment variables are96     set then the locale defaults to <code class="literal">C</code>.97    </p><p>98     Some message localization libraries also look at the environment99     variable <code class="envar">LANGUAGE</code> which overrides all other locale100     settings for the purpose of setting the language of messages.  If101     in doubt, please refer to the documentation of your operating102     system, in particular the documentation about103     <span class="application">gettext</span>.104    </p></div><p>105    To enable messages to be translated to the user's preferred language,106    <acronym class="acronym">NLS</acronym> must have been selected at build time107    (<code class="literal">configure --enable-nls</code>).  All other locale support is108    built in automatically.109   </p></div><div class="sect2" id="LOCALE-BEHAVIOR"><div class="titlepage"><div><div><h3 class="title">24.1.2. Behavior <a href="#LOCALE-BEHAVIOR" class="id_link">#</a></h3></div></div></div><p>110    The locale settings influence the following SQL features:111 112    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>113       Sort order in queries using <code class="literal">ORDER BY</code> or the standard114       comparison operators on textual data115       <a id="id-1.6.11.3.5.2.1.1.1.2" class="indexterm"></a>116      </p></li><li class="listitem"><p>117       The <code class="function">upper</code>, <code class="function">lower</code>, and <code class="function">initcap</code>118       functions119       <a id="id-1.6.11.3.5.2.1.2.1.4" class="indexterm"></a>120       <a id="id-1.6.11.3.5.2.1.2.1.5" class="indexterm"></a>121      </p></li><li class="listitem"><p>122       Pattern matching operators (<code class="literal">LIKE</code>, <code class="literal">SIMILAR TO</code>,123       and POSIX-style regular expressions); locales affect both case124       insensitive matching and the classification of characters by125       character-class regular expressions126       <a id="id-1.6.11.3.5.2.1.3.1.3" class="indexterm"></a>127       <a id="id-1.6.11.3.5.2.1.3.1.4" class="indexterm"></a>128      </p></li><li class="listitem"><p>129       The <code class="function">to_char</code> family of functions130       <a id="id-1.6.11.3.5.2.1.4.1.2" class="indexterm"></a>131      </p></li><li class="listitem"><p>132       The ability to use indexes with <code class="literal">LIKE</code> clauses133      </p></li></ul></div><p>134   </p><p>135    The drawback of using locales other than <code class="literal">C</code> or136    <code class="literal">POSIX</code> in <span class="productname">PostgreSQL</span> is its performance137    impact. It slows character handling and prevents ordinary indexes138    from being used by <code class="literal">LIKE</code>. For this reason use locales139    only if you actually need them.140   </p><p>141    As a workaround to allow <span class="productname">PostgreSQL</span> to use indexes142    with <code class="literal">LIKE</code> clauses under a non-C locale, several custom143    operator classes exist. These allow the creation of an index that144    performs a strict character-by-character comparison, ignoring145    locale comparison rules. Refer to <a class="xref" href="indexes-opclass.html" title="11.10. Operator Classes and Operator Families">Section 11.10</a>146    for more information.  Another approach is to create indexes using147    the <code class="literal">C</code> collation, as discussed in148    <a class="xref" href="collation.html" title="24.2. Collation Support">Section 24.2</a>.149   </p></div><div class="sect2" id="LOCALE-SELECTING-LOCALES"><div class="titlepage"><div><div><h3 class="title">24.1.3. Selecting Locales <a href="#LOCALE-SELECTING-LOCALES" class="id_link">#</a></h3></div></div></div><p>150    Locales can be selected in different scopes depending on requirements.151    The above overview showed how locales are specified using152    <code class="command">initdb</code> to set the defaults for the entire cluster.  The153    following list shows where locales can be selected.  Each item provides154    the defaults for the subsequent items, and each lower item allows155    overriding the defaults on a finer granularity.156   </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>157      As explained above, the environment of the operating system provides the158      defaults for the locales of a newly initialized database cluster.  In159      many cases, this is enough: If the operating system is configured for160      the desired language/territory, then161      <span class="productname">PostgreSQL</span> will by default also behave162      according to that locale.163     </p></li><li class="listitem"><p>164      As shown above, command-line options for <code class="command">initdb</code>165      specify the locale settings for a newly initialized database cluster.166      Use this if the operating system does not have the locale configuration167      you want for your database system.168     </p></li><li class="listitem"><p>169      A locale can be selected separately for each database.  The SQL command170      <code class="command">CREATE DATABASE</code> and its command-line equivalent171      <code class="command">createdb</code> have options for that.  Use this for example172      if a database cluster houses databases for multiple tenants with173      different requirements.174     </p></li><li class="listitem"><p>175      Locale settings can be made for individual table columns.  This uses an176      SQL object called <em class="firstterm">collation</em> and is explained in177      <a class="xref" href="collation.html" title="24.2. Collation Support">Section 24.2</a>.  Use this for example to sort data in178      different languages or customize the sort order of a particular table.179     </p></li><li class="listitem"><p>180      Finally, locales can be selected for an individual query.  Again, this181      uses SQL collation objects.  This could be used to change the sort order182      based on run-time choices or for ad-hoc experimentation.183     </p></li></ol></div></div><div class="sect2" id="LOCALE-PROVIDERS"><div class="titlepage"><div><div><h3 class="title">24.1.4. Locale Providers <a href="#LOCALE-PROVIDERS" class="id_link">#</a></h3></div></div></div><p>184    <span class="productname">PostgreSQL</span> supports multiple <em class="firstterm">locale185    providers</em>.  This specifies which library supplies the locale186    data.  One standard provider name is <code class="literal">libc</code>, which uses187    the locales provided by the operating system C library.  These are the188    locales used by most tools provided by the operating system.  Another189    provider is <code class="literal">icu</code>, which uses the external190    ICU<a id="id-1.6.11.3.7.2.5" class="indexterm"></a> library.  ICU locales can191    only be used if support for ICU was configured when PostgreSQL was built.192   </p><p>193    The commands and tools that select the locale settings, as described194    above, each have an option to select the locale provider.  The examples195    shown earlier all use the <code class="literal">libc</code> provider, which is the196    default.  Here is an example to initialize a database cluster using the197    ICU provider:198</p><pre class="programlisting">199initdb --locale-provider=icu --icu-locale=en200</pre><p>201    See the description of the respective commands and programs for202    details.  Note that you can mix locale providers at different203    granularities, for example use <code class="literal">libc</code> by default for the204    cluster but have one database that uses the <code class="literal">icu</code>205    provider, and then have collation objects using either provider within206    those databases.207   </p><p>208    Which locale provider to use depends on individual requirements.  For most209    basic uses, either provider will give adequate results.  For the libc210    provider, it depends on what the operating system offers; some operating211    systems are better than others.  For advanced uses, ICU offers more locale212    variants and customization options.213   </p></div><div class="sect2" id="ICU-LOCALES"><div class="titlepage"><div><div><h3 class="title">24.1.5. ICU Locales <a href="#ICU-LOCALES" class="id_link">#</a></h3></div></div></div><div class="sect3" id="ICU-LOCALE-NAMES"><div class="titlepage"><div><div><h4 class="title">24.1.5.1. ICU Locale Names <a href="#ICU-LOCALE-NAMES" class="id_link">#</a></h4></div></div></div><p>214     The ICU format for the locale name is a <a class="link" href="locale.html#ICU-LANGUAGE-TAG" title="24.1.5.3. Language Tag">Language Tag</a>.215 216</p><pre class="programlisting">217CREATE COLLATION mycollation1 (provider = icu, locale = 'ja-JP');218CREATE COLLATION mycollation2 (provider = icu, locale = 'fr');219</pre><p>220    </p></div><div class="sect3" id="ICU-CANONICALIZATION"><div class="titlepage"><div><div><h4 class="title">24.1.5.2. Locale Canonicalization and Validation <a href="#ICU-CANONICALIZATION" class="id_link">#</a></h4></div></div></div><p>221     When defining a new ICU collation object or database with ICU as the222     provider, the given locale name is transformed ("canonicalized") into a223     language tag if not already in that form. For instance,224 225</p><pre class="screen">226CREATE COLLATION mycollation3 (provider = icu, locale = 'en-US-u-kn-true');227NOTICE:  using standard form "en-US-u-kn" for locale "en-US-u-kn-true"228CREATE COLLATION mycollation4 (provider = icu, locale = 'de_DE.utf8');229NOTICE:  using standard form "de-DE" for locale "de_DE.utf8"230</pre><p>231 232     If you see this notice, ensure that the <code class="symbol">provider</code> and233     <code class="symbol">locale</code> are the expected result. For consistent results234     when using the ICU provider, specify the canonical <a class="link" href="locale.html#ICU-LANGUAGE-TAG" title="24.1.5.3. Language Tag">language tag</a> instead of relying on the235     transformation.236    </p><p>237     A locale with no language name, or the special language name238     <code class="literal">root</code>, is transformed to have the language239     <code class="literal">und</code> ("undefined").240    </p><p>241     ICU can transform most libc locale names, as well as some other formats,242     into language tags for easier transition to ICU. If a libc locale name is243     used in ICU, it may not have precisely the same behavior as in libc.244    </p><p>245     If there is a problem interpreting the locale name, or if the locale name246     represents a language or region that ICU does not recognize, you will see247     the following warning:248 249</p><pre class="screen">250CREATE COLLATION nonsense (provider = icu, locale = 'nonsense');251WARNING:  ICU locale "nonsense" has unknown language "nonsense"252HINT:  To disable ICU locale validation, set parameter icu_validation_level to DISABLED.253CREATE COLLATION254</pre><p>255 256     <a class="xref" href="runtime-config-client.html#GUC-ICU-VALIDATION-LEVEL">icu_validation_level</a> controls how the message is257     reported. Unless set to <code class="literal">ERROR</code>, the collation will258     still be created, but the behavior may not be what the user intended.259    </p></div><div class="sect3" id="ICU-LANGUAGE-TAG"><div class="titlepage"><div><div><h4 class="title">24.1.5.3. Language Tag <a href="#ICU-LANGUAGE-TAG" class="id_link">#</a></h4></div></div></div><p>260     A language tag, defined in BCP 47, is a standardized identifier used to261     identify languages, regions, and other information about a locale.262    </p><p>263     Basic language tags are simply264     <em class="replaceable"><code>language</code></em><code class="literal">-</code><em class="replaceable"><code>region</code></em>;265     or even just <em class="replaceable"><code>language</code></em>. The266     <em class="replaceable"><code>language</code></em> is a language code267     (e.g. <code class="literal">fr</code> for French), and268     <em class="replaceable"><code>region</code></em> is a region code269     (e.g. <code class="literal">CA</code> for Canada). Examples:270     <code class="literal">ja-JP</code>, <code class="literal">de</code>, or271     <code class="literal">fr-CA</code>.272    </p><p>273     Collation settings may be included in the language tag to customize274     collation behavior. ICU allows extensive customization, such as275     sensitivity (or insensitivity) to accents, case, and punctuation;276     treatment of digits within text; and many other options to satisfy a277     variety of uses.278    </p><p>279     To include this additional collation information in a language tag,280     append <code class="literal">-u</code>, which indicates there are additional281     collation settings, followed by one or more282     <code class="literal">-</code><em class="replaceable"><code>key</code></em><code class="literal">-</code><em class="replaceable"><code>value</code></em>283     pairs. The <em class="replaceable"><code>key</code></em> is the key for a <a class="link" href="collation.html#ICU-COLLATION-SETTINGS" title="24.2.3.2. Collation Settings for an ICU Locale">collation setting</a> and284     <em class="replaceable"><code>value</code></em> is a valid value for that setting. For285     boolean settings, the <code class="literal">-</code><em class="replaceable"><code>key</code></em>286     may be specified without a corresponding287     <code class="literal">-</code><em class="replaceable"><code>value</code></em>, which implies a288     value of <code class="literal">true</code>.289    </p><p>290     For example, the language tag <code class="literal">en-US-u-kn-ks-level2</code>291     means the locale with the English language in the US region, with292     collation settings <code class="literal">kn</code> set to <code class="literal">true</code>293     and <code class="literal">ks</code> set to <code class="literal">level2</code>. Those294     settings mean the collation will be case-insensitive and treat a sequence295     of digits as a single number:296 297</p><pre class="screen">298CREATE COLLATION mycollation5 (provider = icu, deterministic = false, locale = 'en-US-u-kn-ks-level2');299SELECT 'aB' = 'Ab' COLLATE mycollation5 as result;300 result301--------302 t303(1 row)304 305SELECT 'N-45' &lt; 'N-123' COLLATE mycollation5 as result;306 result307--------308 t309(1 row)310</pre><p>311    </p><p>312     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 additional313     examples of using language tags with custom collation information for the314     locale.315    </p></div></div><div class="sect2" id="LOCALE-PROBLEMS"><div class="titlepage"><div><div><h3 class="title">24.1.6. Problems <a href="#LOCALE-PROBLEMS" class="id_link">#</a></h3></div></div></div><p>316    If locale support doesn't work according to the explanation above,317    check that the locale support in your operating system is318    correctly configured.  To check what locales are installed on your319    system, you can use the command <code class="literal">locale -a</code> if320    your operating system provides it.321   </p><p>322    Check that <span class="productname">PostgreSQL</span> is actually using the locale323    that you think it is.  The <code class="envar">LC_COLLATE</code> and <code class="envar">LC_CTYPE</code>324    settings are determined when a database is created, and cannot be325    changed except by creating a new database.  Other locale326    settings including <code class="envar">LC_MESSAGES</code> and <code class="envar">LC_MONETARY</code>327    are initially determined by the environment the server is started328    in, but can be changed on-the-fly.  You can check the active locale329    settings using the <code class="command">SHOW</code> command.330   </p><p>331    The directory <code class="filename">src/test/locale</code> in the source332    distribution contains a test suite for333    <span class="productname">PostgreSQL</span>'s locale support.334   </p><p>335    Client applications that handle server-side errors by parsing the336    text of the error message will obviously have problems when the337    server's messages are in a different language.  Authors of such338    applications are advised to make use of the error code scheme339    instead.340   </p><p>341    Maintaining catalogs of message translations requires the on-going342    efforts of many volunteers that want to see343    <span class="productname">PostgreSQL</span> speak their preferred language well.344    If messages in your language are currently not available or not fully345    translated, your assistance would be appreciated.  If you want to346    help, refer to <a class="xref" href="nls.html" title="Chapter 57. Native Language Support">Chapter 57</a> or write to the developers'347    mailing list.348   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="charset.html" title="Chapter 24. Localization">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="collation.html" title="24.2. Collation Support">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 24. Localization </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.2. Collation Support</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai