From: Tom Lane <tgl@sss.pgh.pa.us>
To: Adrian Klaver <adrian.klaver@aklaver.com>
cc: Troels Arvin <troels@arvin.dk>, pgsql-general@lists.postgresql.org
Subject: Re: utf8 vs UTF-8
In-reply-to: <f510e041-7e9b-4745-847b-06b9dcce6281@aklaver.com>
References: <fb95f8f7-f57b-46a8-bc9d-ea13433474e3@arvin.dk> <2388205.1715953909@sss.pgh.pa.us> <89165125-54b6-46a2-9b2c-0a7e275596bf@arvin.dk> <f510e041-7e9b-4745-847b-06b9dcce6281@aklaver.com>
Comments: In-reply-to Adrian Klaver <adrian.klaver@aklaver.com>
	message dated "Sat, 18 May 2024 08:01:17 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2763158.1716045784.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Sat, 18 May 2024 11:23:04 -0400
Message-ID: <2763159.1716045784@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2763159.1716045784%40sss.pgh.pa.us>
Precedence: bulk

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 5/18/24 07:48, Troels Arvin wrote:
>> Also, when I try to create a database with "en_US.utf8" as locale 
>> without specifying a template:
>> 
>> troels=# create database test4 locale 'en_US.utf8';
>> ERROR:  new collation (en_US.utf8) is incompatible with the collation of 
>> the template database (en_US.UTF-8)
>> HINT:  Use the same collation as in the template database, or use 
>> template0 as template.

> I'm going to say that is Postgres being exact to a fault.

Yeah.  glibc will treat those two locale names as equivalent,
and I think most if not all other libc implementations do too.
But Postgres doesn't know that so it demands exact textual
equality before assuming two locale names are equivalent.

If this is getting in your way you could probably get away with
just UPDATE-ing pg_database to use whichever spelling you think is
preferable; the strings appearing in datcollate and datctype aren't
stored anywhere else.  (But experiment in a scratch installation to
verify that ... and don't try changing them to something that you
don't know to be semantically equivalent.)

			regards, tom lane