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
