Re: ICU, locale and collation question

On 10.05.23 07:02, Kirk Wolak wrote:
> On Tue, May 9, 2023 at 11:24 AM Peter Eisentraut
> <peter(dot)eisentraut(at)enterprisedb(dot)com
> <mailto:peter(dot)eisentraut(at)enterprisedb(dot)com>> wrote:
>
> On 09.05.23 08:54, Oscar Carlberg wrote:
> > Our initdb setup would then look like this for compatibility;
> > -E 'UTF-8'
> > --locale-provider=icu
> > --icu-locale=sv-SE-x-icu
> > --lc_monetary=sv_SE.UTF-8
> > --lc-numeric=sv_SE.UTF-8
> > --lc-time=sv_SE.UTF-8
> > --lc-messages=en_US.UTF-8
> >
> > Should we still provide createdb with --lc-collate=C and
> --lc-ctype=C,
> > or should we set those to sv_SE.UTF-8 as well?
>
> You should set those to something other than C.  It doesn't matter much
> what exactly, so what you have there is fine.
>
> Setting it to C would for example affect the ability of the text search
> functionality to detect words containing non-ASCII characters.
>
> Doesn't searching LIKE 'abc%'  behave much better for C than others.
> This was the driving force for choosing C for us.
> [EXPLAIN made it clear that it was range bound until 'abd']

For that use, I would recommend making an index specifically on the
tables you need, instead of switching the whole database.

Also, if you are using the ICU provider for the database, then setting
lc_collation=C wouldn't even affect LIKE optimization, because the ICU
locale would be used.