Message-ID: <2cd2cf04ba6ea21678b893dbfebe97bcf82726dc.camel@j-davis.com>
Subject: Re: LOCALE C.UTF-8 on EDB Windows v17 server
From: Jeff Davis <pgsql@j-davis.com>
To: Dominique Devienne <ddevienne@gmail.com>, pgsql-general@postgresql.org
Date: Wed, 04 Jun 2025 19:52:14 -0700
In-Reply-To: <CAFCRh--rtqbOBpJYFDmPD9kYCYxsxKpLW7LHxYMYhHXa2XoStw@mail.gmail.com>
References: 
	<CAFCRh--rtqbOBpJYFDmPD9kYCYxsxKpLW7LHxYMYhHXa2XoStw@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.3-0ubuntu1 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/2cd2cf04ba6ea21678b893dbfebe97bcf82726dc.camel%40j-davis.com>
Precedence: bulk

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
>=20
> 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