Message-ID: <bdce746ad6052774fbc0de138e7c4fcc77886e77.camel@cybertec.at>
Subject: Re: Choosing default collation/ctype
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Daniel Verite <daniel@manitou-mail.org>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>, pgsql-general@lists.postgresql.org
Date: Mon, 04 May 2026 22:45:16 +0200
In-Reply-To: <627add7e-94df-49ca-aa12-ae3900b7945f@manitou-mail.org>
References: <627add7e-94df-49ca-aa12-ae3900b7945f@manitou-mail.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.3 (3.58.3-1.fc43) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/bdce746ad6052774fbc0de138e7c4fcc77886e77.camel%40cybertec.at>
Precedence: bulk

On Mon, 2026-05-04 at 21:34 +0200, Daniel Verite wrote:
> Laurenz Albe wrote:
>=20
> > > Then choose UTF8.
> >=20
> > Right!=C2=A0 And I recommend "C" for the collation.
>=20
> Yet the "C" collation is unsuitable for handling character types
> beyond ASCII.
> For instance, it considers that accented letters are not letters,
> so upper('=C3=A9t=C3=A9') is '=C3=A9T=C3=A9' instead of '=C3=89T=C3=89', =
and '=C3=A9' ~ '\w' is false.
>=20
> C.UTF-8 solves that, and since Postgres 17, it's available for all operat=
ing
> systems with the builtin provider.
> So if you target Postgres 17+, C.UTF-8 from the builtin provider is
> a better choice for UTF-8 databases than "C" .

Yes, "builtin" and the "C" collation is the best default value.

Yours,
Laurenz Albe