Subject: Re: Names of encodings, lc_collate, lc_ctype
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@lists.postgresql.org
References: <3da4af02269b44a18b56a070af0bca18@jakobs.com>
 <810.1562765208@sss.pgh.pa.us>
From: Holger Jakobs <holger@jakobs.com>
Openpgp: preference=signencrypt
Autocrypt: addr=holger@jakobs.com; prefer-encrypt=mutual; keydata=
 mQGiBEG8IA8RBAC6WqUzEPTjest7MvFca5WlI47EgtKzOy1D1X9gSEmPj0sQrilNGEitY/+Q
 kPnCvI3odz1XXf/MZQtXwlxJA4lPmx8/K7MqBj9vh0J5jRznpE7l+SBDmmDobIqBAgl6BZvH
 1C7e2y72h5T1/plrXonLUGzthqBtsvWl0ogoyTJqiwCg6AOhYX+Xbcl8/AXy4F4qbUq4LXsD
 /j6yCKRkyleN688y9YpuS5J6/ZZ/OBCM74pa2iNge3GmXn1JeUowF693QFCGHkpswmDIoGhL
 5iM6GHj9HFB6rTXW5H3sR1y6ta7vXwoNBGkgiM89B1a/KsLT5L6MZgZ5AV0tj6vxwAsuLixS
 SDiNVNAd8vp2FfqdLlOgThsgVIwKBACsCzZa1VxpdoNSYnkTOSZhEjZkbWApdneQ2bTxXJgW
 +fWgVeLiojCp6I1AmQqzwHz8UN1nrsQjH8Rnxt2J/C5H/Ek9jlOFakFQXlPMZw3tNeTA1P3B
 3zPyq6hKFPZHFklUquE3gHVjnX9qxSW0xvFirf6xAMCMYWRnCQHRgiXoAbQhSG9sZ2VyIEph
 a29icyA8aG9sZ2VyQGpha29icy5jb20+iGQEExECACQCGwMGCwkIBwMCAxUCAwMWAgECHgEC
 F4AFAlZ7KnEFCSeLDWIACgkQileck7MZYbpbxgCgxM54+4qx/sfGwq01x93pWr2QghwAoIfD
 Of6Uoc43y98+xR5/Iu9Z1RH8uQENBEG8IBAQBACiGkoX8HKCocdCwRdMzc9siDs1w0YNayhT
 PnK9Ii3wzzS2R9NSyWsPQVyx6hkVfSmK6CE5RzfuDUzrYEgyaV/2vT3JD9Gko+RuFnjeQvVo
 8hc19bmBSn95ATki+hFy/68MqpMlsG7YyCluRSpXh2qxlhbh2FzTIQmiEQAR4kFCEwADBQP/
 Z1Y6nwoXw/5JmLJLFcvZZlFfn4g9TjyMSwH50/AXqOApccy9JSzoTFY87ItmGkhD0qj18PJB
 St2uqASGrk2/55XK596RWnoeyL9mzj5r6woDqPNRXIbK85foGVIMRrb/EufgSOP5H6VHybvB
 PD4CppEw4Q0fRsMCaOcvibkjHoqITwQYEQIADwIbDAUCVnsqcQUJJ4sNYQAKCRCKV5yTsxlh
 urunAJ9avyijdRls7l6XPc2cBMys3xu/sgCeOVJE6DzwBBRUd6S6mtf2eG4FPXg=
Message-ID: <bdb6898d-2c8b-3604-dc5a-f5e52823a34f@jakobs.com>
Date: Wed, 10 Jul 2019 21:29:59 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.7.1
MIME-Version: 1.0
In-Reply-To: <810.1562765208@sss.pgh.pa.us>
Content-Type: multipart/alternative;
 boundary="------------CC448E49D6C0121ABA1B3C89"
Content-Language: de-DE
Precedence: bulk

This is a multi-part message in MIME format.
--------------CC448E49D6C0121ABA1B3C89
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit

Dear Tom,

After creating the locale with

  sudo locale-gen en_US.UTF-8

and a restart of the PostgreSQL server, it worked.

Thank you.

Holger Jakobs

Am 10.07.19 um 15:26 schrieb Tom Lane:
> Holger Jakobs <holger@jakobs.com> writes:
>>    CREATE DATABASE db1 WITH TEMPLATE = template0 ENCODING = 'UTF8' 
>> LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
>> which causes trouble on a PostgreSQL 10 or 11 on an Ubuntu 18.04 machine
>>    ungültiger Locale-Name: »en_US.UTF-8«  (meaning 'illegal locale name')
> Hmm, does "locale -a" show that you have en_US installed?
>
> It's basically on the platform's libc to say whether the values for
> LC_COLLATE and LC_CTYPE are valid.  In my experience, glibc is quite
> forgiving about how the encoding suffix is spelled, so I'm wondering
> if your destination machine is simply lacking the locale definition.
>
>> The command
>>    select * from pg_collation;
>> shows (among many others of course)
>>    en_US.utf8
> This doesn't have anything to do with what CREATE DATABASE accepts,
> IIRC.  It does show that when initdb ran, it saw en_US.utf8 reported
> by "locale -a"; but maybe that was in a different environment.
>
>> How come there are encodings/collations/locales with and without hyphen? 
>> Why does the Ubuntu machine not accept a locale which is present in 
>> lc_collation?
> Interesting questions, but you need a glibc expert not a Postgres
> expert.
>
> 			regards, tom lane
-- 

Holger Jakobs, Bergisch Gladbach
instant messaging: xmpp:holger@jakobs.com
+49 178 9759012 <tel:+491789759012> oder +49 2202 817157
<tel:+492202817157>


--------------CC448E49D6C0121ABA1B3C89
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <p>Dear Tom,</p>
    <p>After creating the locale with</p>
    <p>  sudo locale-gen en_US.UTF-8</p>
    <p>and a restart of the PostgreSQL server, it worked.</p>
    <p>Thank you.</p>
    <p>Holger Jakobs<br>
    </p>
    <div class="moz-cite-prefix">Am 10.07.19 um 15:26 schrieb Tom Lane:<br>
    </div>
    <blockquote type="cite" cite="mid:810.1562765208@sss.pgh.pa.us">
      <pre class="moz-quote-pre" wrap="">Holger Jakobs <a class="moz-txt-link-rfc2396E" href="mailto:holger@jakobs.com">&lt;holger@jakobs.com&gt;</a> writes:
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">   CREATE DATABASE db1 WITH TEMPLATE = template0 ENCODING = 'UTF8' 
LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
which causes trouble on a PostgreSQL 10 or 11 on an Ubuntu 18.04 machine
   ungültiger Locale-Name: »en_US.UTF-8«  (meaning 'illegal locale name')
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
Hmm, does "locale -a" show that you have en_US installed?

It's basically on the platform's libc to say whether the values for
LC_COLLATE and LC_CTYPE are valid.  In my experience, glibc is quite
forgiving about how the encoding suffix is spelled, so I'm wondering
if your destination machine is simply lacking the locale definition.

</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">The command
   select * from pg_collation;
shows (among many others of course)
   en_US.utf8
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
This doesn't have anything to do with what CREATE DATABASE accepts,
IIRC.  It does show that when initdb ran, it saw en_US.utf8 reported
by "locale -a"; but maybe that was in a different environment.

</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">How come there are encodings/collations/locales with and without hyphen? 
Why does the Ubuntu machine not accept a locale which is present in 
lc_collation?
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
Interesting questions, but you need a glibc expert not a Postgres
expert.

			regards, tom lane
</pre>
    </blockquote>
    <div class="moz-signature">-- <br>
      <p>Holger Jakobs, Bergisch Gladbach<br>
        instant messaging: <a href="xmpp:holger@jakobs.com">xmpp:holger@jakobs.com</a><br>
        <a href="tel:+491789759012">+49 178 9759012</a> oder <a
          href="tel:+492202817157">+49 2202 817157</a>
      </p>
    </div>
  </body>
</html>

--------------CC448E49D6C0121ABA1B3C89--