Message-ID: <a806bf1edc08639bc58c6a5ade725049a4f61398.camel@j-davis.com>
Subject: Re: pgsql: Add standard collation UNICODE
From: Jeff Davis <pgsql@j-davis.com>
To: "Jonathan S. Katz" <jkatz@postgresql.org>
Cc: Peter Eisentraut <peter@eisentraut.org>,
	pgsql-committers@lists.postgresql.org
Date: Fri, 10 Mar 2023 13:24:27 -0800
In-Reply-To: <6648546F-52B6-4525-A13B-414D99655033@postgresql.org>
References: <582e43d0a814e09c62ea662dd851f43bfc137a16.camel@j-davis.com>
	 <6648546F-52B6-4525-A13B-414D99655033@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.44.4-0ubuntu1 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/a806bf1edc08639bc58c6a5ade725049a4f61398.camel%40j-davis.com>
Precedence: bulk

On Fri, 2023-03-10 at 11:52 -0800, Jonathan S. Katz wrote:
>=20
> The capitals should be first.

That is not true in a lot of natural language locales, whether libc or
ICU. The following return true for me (collencoding UTF-8):

  select 'abc' collate "en_US" < 'ABC' collate "en_US";
  select 'abc' collate "fr_FR" < 'ABC' collate "fr_FR";
  select 'abc' collate "de_DE" < 'ABC' collate "de_DE";
  select 'abc' collate "de_AT" < 'ABC' collate "de_AT";
  select 'abc' collate "es_ES" < 'ABC' collate "es_ES";
  select 'abc' collate "en-US-x-icu" < 'ABC' collate "en-US-x-icu";
  select 'abc' collate "fr-CA-x-icu" < 'ABC' collate "fr-CA-x-icu";
  select 'abc' collate "ja-JP-x-icu" < 'ABC' collate "ja-JP-x-icu";
  select 'abc' collate "tr-TR-x-icu" < 'ABC' collate "tr-TR-x-icu";

There are some cases that return false, as well:

  select 'abc' collate "ja_JP" < 'ABC' collate "ja_JP";
  select 'abc' collate "fr_CA" < 'ABC' collate "fr_CA";
  select 'abc' collate "en-US-u-va-posix-x-icu" <=C2=A0
    'ABC' collate "en-US-u-va-posix-x-icu";

The cases where it's false appear to be more common in libc locales,
but most libc locales that I tested still sort lowercase first.

Regards,
	Jeff Davis