On Wed, 2025-06-04 at 14:23 +0200, Dominique Devienne wrote:
> create database "dd_v168" encoding 'UTF8' locale 'C.UTF-8'
> locale_provider 'builtin' template template0
I believe what you want is:
create database "dd_v168" encoding 'UTF8' builtin_locale 'C.UTF-8'
locale_provider 'builtin' template template0
notice "builtin_locale" instead of "locale".
CREATE DATABASE options are confusing due to some historical reasons.
LOCALE gets expanded (effectively) into LC_COLLATE and LC_CTYPE, but
the ICU and builtin providers don't use those for collation. Therefore,
we have ICU_LOCALE and BUILTIN_LOCALE to specify the locales for those
providers. Sorry -- I know that's confusing, but I don't know a good
way to fix it without breaking previous commands.
> On Windows, I'm getting
I tried on windows and linux and got the same result.
> I tried instead
>
> create database "dd_v168" encoding 'UTF8' locale 'pg_c_utf8'
> locale_provider 'builtin' template template0
The previous name was correct. "pg_c_utf8" is the identifier for the
collation object in pg_collation.
> Second, I'm not confused between locales and collations!
Locales are the names as the provider understands them; the collation
is an object in pg_collation that points to that locale (along with
metadata). For instance, there's a collation named "en_US" in
pg_collation, which points to the libc provider and locale name
'en_US.utf8'. There's also a collation named "en-US-x-icu" in
pg_collation which points to the ICU provider and locale name 'en-US'.
In other words, the collation objects are a layer of indirection to
what the provider actually understands.
Regards,
Jeff Davis
