MIME-Version: 1.0
References: <CAFCRh--rtqbOBpJYFDmPD9kYCYxsxKpLW7LHxYMYhHXa2XoStw@mail.gmail.com>
 <f292cf26e515b425ff35b5ad3eab0d6a8e4d385c.camel@cybertec.at>
In-Reply-To: <f292cf26e515b425ff35b5ad3eab0d6a8e4d385c.camel@cybertec.at>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Thu, 5 Jun 2025 10:53:14 +0200
Message-ID: <CAFCRh--iJ4MT2exvXnvRPum+7su3Jn9qmnBQqxWsAtyEyEo83A@mail.gmail.com>
Subject: Re: LOCALE C.UTF-8 on EDB Windows v17 server
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-general@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh--iJ4MT2exvXnvRPum%2B7su3Jn9qmnBQqxWsAtyEyEo83A%40mail.gmail.com>
Precedence: bulk

On Thu, Jun 5, 2025 at 3:01=E2=80=AFAM Laurenz Albe <laurenz.albe@cybertec.=
at> wrote:
> On Wed, 2025-06-04 at 14:23 +0200, Dominique Devienne wrote:
> > 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
>
> Pilot error.  If you use "LOCALE_PROVIDER builtin", you have to specify
> BUILTIN LOCALE too:
>
> CREATE DATABASE b
>    TEMPLATE template0
>    LOCALE_PROVIDER builtin
>    BUILTIN_LOCALE 'C.UTF-8'
>    /* used for aspects other than collation and character type */
>    LOCALE 'C';

Thanks Laurenz. Indeed, Using LOCALE vs BUILTIN_LOCALE matters.

On Linux, no error unlike on Windows (still inconsistent there IMHO),
but the result is slightly different for datcollate and datctype (C vs en_U=
S),
while the same for datlocprovider and datlocale, what I looked at.

Thus I kinda persist that there *is* a portability issue here.

Also, note what the doc says:

If locale_provider is builtin, then locale or builtin_locale must be
specified and set to either C or C.UTF-8.

It clearly says "locale or builtin_locale", emphasis on the OR.

So two issues here.
1) the doc is wrong or misleading on this.
2) the same command works on Linux, but not Windows.

FWIW. --DD

C:\Users\ddevienne>psql service=3Dpau17
psql (17.4, server 17.5)
ddevienne=3D> select version();
                                                 version
---------------------------------------------------------------------------=
------------------------------
 PostgreSQL 17.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0
20210514 (Red Hat 8.5.0-26), 64-bit
(1 row)

ddevienne=3D> create database "dd_v168" encoding 'UTF8' locale 'C.UTF-8'
ddevienne-> locale_provider 'builtin' template template0;
CREATE DATABASE
ddevienne=3D> select datlocprovider, datlocale, datcollate, datctype
from pg_database where datname =3D 'dd_v168';
 datlocprovider | datlocale | datcollate | datctype
----------------+-----------+------------+----------
 b              | C.UTF-8   | C.UTF-8    | C.UTF-8
(1 row)

ddevienne=3D> create database "dd_v168b" encoding 'UTF8' builtin_locale 'C.=
UTF-8'
ddevienne-> locale_provider 'builtin' template template0;
CREATE DATABASE
ddevienne=3D> select datlocprovider, datlocale, datcollate, datctype
from pg_database where datname =3D 'dd_v168b';
 datlocprovider | datlocale | datcollate  |  datctype
----------------+-----------+-------------+-------------
 b              | C.UTF-8   | en_US.UTF-8 | en_US.UTF-8
(1 row)