MIME-Version: 1.0
From: Dominique Devienne <ddevienne@gmail.com>
Date: Wed, 4 Jun 2025 14:23:17 +0200
Message-ID: <CAFCRh--rtqbOBpJYFDmPD9kYCYxsxKpLW7LHxYMYhHXa2XoStw@mail.gmail.com>
Subject: LOCALE C.UTF-8 on EDB Windows v17 server
To: pgsql-general@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAFCRh--rtqbOBpJYFDmPD9kYCYxsxKpLW7LHxYMYhHXa2XoStw%40mail.gmail.com>
Precedence: bulk

Hi.

For a long time, I was just doing a plain CREATE DATABASE, using
whatever local template1 was using.

Then when v17 came out, I wanted to force UTF-8 for the encoding,
*AND* use the new C.UTF-8 builtin locale, to not depend on libc on
Linux. (which forced me to use template0 explicitly). And since it was
builtin, I just assumed Windows would be fine too. But it's not
apparently not...

I'm using the official v17.5 EDB installer, for the Windows server.

The command I'm using (from a libpq trace) is:

create database "dd_v168" encoding 'UTF8' locale 'C.UTF-8'
locale_provider 'builtin' template template0

On Windows, I'm getting

2025-06-04 14:07:41.227419 B 155 ErrorResponse S "ERROR" V "ERROR" C
"42809" M "invalid LC_COLLATE locale name: "C.UTF-8"" H "If the locale
name is specific to ICU, use ICU_LOCALE." F "dbcommands.c" L "1057" R
"createdb" \x00

Given that v17.5 Windows returns

postgres=# select * from pg_collation where collname ilike '%utf%';
 oid | collname  | collnamespace | collowner | collprovider |
collisdeterministic | collencoding | collcollate | collctype |
colllocale | collicurules | collversion
-----+-----------+---------------+-----------+--------------+---------------------+--------------+-------------+-----------+------------+--------------+-------------
 811 | pg_c_utf8 |            11 |        10 | b            | t
           |            6 |             |           | C.UTF-8    |
         | 1
(1 row)

While Linux returns:

ddevienne=> select * from pg_collation where collname ilike '%utf%';
  oid  |    collname     | collnamespace | collowner | collprovider |
collisdeterministic | collencoding |   collcollate   |    collctype
| colllocale | collicurules | collversion
-------+-----------------+---------------+-----------+--------------+---------------------+--------------+-----------------+-----------------+------------+--------------+-------------
   811 | pg_c_utf8       |            11 |        10 | b            |
t                   |            6 |                 |
| C.UTF-8    |              | 1
 12351 | C.utf8          |            11 |        10 | c            |
t                   |            6 | C.utf8          | C.utf8
|            |              |
 12354 | en_AU.utf8      | ...
(17 rows)

I tried instead

create database "dd_v168" encoding 'UTF8' locale 'pg_c_utf8'
locale_provider 'builtin' template template0

but that's still KO with

2025-06-04 14:10:58.748508 B 157 ErrorResponse S "ERROR" V "ERROR" C
"42809" M "invalid LC_COLLATE locale name: "pg_c_utf8"" H "If the
locale name is specific to ICU, use ICU_LOCALE." F "dbcommands.c" L
"1057" R "createdb" \x00

First, given the above, I'm no longer sure what I was doing on Linux
was actually using the new built-in collation.

Second, I'm not confused between locales and collations!

What the command supposed to work portably with v17 on both Linux and
Windows server, that uses the new built-in collations (and locale???)

I'd appreciate some clarifications please. --DD