Subject: Re: Choosing default collation/ctype

From: Laurenz Albe <[email-masked]>
Date: Tue, 05 May 2026 13:31:00 +0200

On Tue, 2026-05-05 at 13:16 +0200, Daniel Verite wrote:
> Laurenz Albe wrote:
> 
> > > 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.
> 
> But my point was that, no, it's not.
> Let's show a concrete example with Postgres 18:
> 
> [...]
>
> It is not the correct uppercasing.

That is true.
But if you are using "C.UTF-8", the semantics of upper() can change
between versions, if Unicode is upgraded.  That bears a residual risk
of OS upgrades breaking indexes on upper(col).

I'd say that the small benefit of better case conversion isn't worth
the risk.  I'd chose "C", and use a natural language collation explicitly
on columns where these things matter.

Yours,
Laurenz Albe

---

From: "Daniel Verite" <[email-masked]>
Date: Tue, 05 May 2026 20:28:43 +0200

Laurenz Albe wrote:

> between versions, if Unicode is upgraded. 

Oh I see. Sure, "C" is not affected by that.

> That bears a residual risk

OS upgrades don't count in the case of the builtin provider, but
major Postgres upgrades, yes.

> on columns where these things matter.

While I understanding the reasoning, I'm of the opposite opinion.
To me the lack of Unicode support in "C" is too annoying to make
it a blanket recommendation as the default locale.

Best regards,
-- 
Daniel Vérité 
https://postgresql.verite.pro/