Subject: Re: Choosing default collation/ctype

From: Igor Korot <[email-masked]>
Date: Sun, 3 May 2026 15:52:32 -0500

Hi,

On Sun, May 3, 2026 at 3:09 PM Ron Johnson <[email-masked]> wrote:
>
> On Sun, May 3, 2026 at 3:52 PM Igor Korot <[email-masked]> wrote:
>>
>> Hi, ALL,
>> In the CREATE DATABASE statement I can use encoding/collation/ctype.
>>
>> I can retrieve the encoding list with:
>>
>> [code]
>> SELECT pg_encoding_to_char( conforencoding ) AS name FROM pg_conversion
>> [/code]
>>
>> And then I can get a list of collations/ctypes with:
>>
>> [code]
>> SELECT collname, collencoding, collprovider collctype FROM pg_collation
>> [/code]
>>
>> And then add a logic in my UI to switch collations/ctypes based on encoding.
>>
>> However, what I wonder is:
>>
>> Is there a way to select a default collation/ctype for a specific encoding?
>>
>> Or maybe I'm overthinking it and I should let the user choose and if
>> nothing - just keep those 2 as "Default" and let the server pick it
>> up. However it will be weird, especially from me as a user POV.
>
>
> You know your data, not us.  The first question I'd try to is "how much client text data is not compatible with bog-standard UTF8?"

I don't.
Just trying to create a generic tool to use for people everywhere...

Thank you.

>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!

---

From: Ron Johnson <[email-masked]>
Date: Sun, 3 May 2026 17:05:32 -0400

On Sun, May 3, 2026 at 4:52 PM Igor Korot <[email-masked]> wrote:

> > You know your data, not us.  The first question I'd try to is "how much
> client text data is not compatible with bog-standard UTF8?"
>
> I don't.

Then choose UTF8.

---

From: Laurenz Albe <[email-masked]>
Date: Sun, 03 May 2026 23:17:04 +0200

> > Just trying to create a generic tool to use for people everywhere...

Right!  And I recommend "C" for the collation.
(The user can override the default in column definitions where necessary.)

Yours,
Laurenz Albe

---

From: Igor Korot <[email-masked]>
Date: Sun, 3 May 2026 16:51:32 -0500

Hi, Ron.

>> Just trying to create a generic tool to use for people everywhere...

Let me give you a quick run of what I'm trying to do:

In my code I have 3 combo boxes: encoding, collation and ctype.

Initially they all have a value of "Default".

Lets say a user selected "KOI8-R" as an emcoding.

What I will do is populate collation and ctype combo boxes with
values available for such encodings.
But I want to go a little further and change the values in those
to be the default collation/ctype for the "KOI8-R" encoding.

Now are you saying I should choose the one that have "UTF8"
in it?

Thank you.

---

From: "Daniel Verite" <[email-masked]>
Date: Mon, 04 May 2026 21:34:00 +0200

> Right!  And I recommend "C" for the collation.

Yet the "C" collation is unsuitable for handling character types
beyond ASCII.
For instance, it considers that accented letters are not letters,
so upper('été') is 'éTé' instead of 'ÉTÉ', and 'é' ~ '\w' is false.

C.UTF-8 solves that, and since Postgres 17, it's available for all operating
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" .

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

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 04 May 2026 22:45:16 +0200

> a better choice for UTF-8 databases than "C" .

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

Yours,
Laurenz Albe