Re: Problem in 'ORDER BY' of a column using a created collation? - Mailing list pgsql-hackers

On Mon, Aug 25, 2025 at 8:59 PM jian he <jian.universality@gmail.com> wrote:

On Mon, Aug 25, 2025 at 3:52 PM Nishant Sharma
<nishant.sharma@enterprisedb.com> wrote:
>
>
> Experiment 1:-
> SQL File : PG_Exp_1.sql
>
> Actual Output : PG_Exp_1.out
>
> Created COLLATION : CREATE COLLATION test_coll (
> provider = icu, locale = 'ja-u-kr-latn-digit');
>
> Experiment 2:-
> SQL File : PG_Exp_2.sql
>
> Actual Output : PG_Exp_2.out
>
> Created 'COLLATION' : CREATE COLLATION test_coll (
> provider = icu, locale = 'ja-u-kr-digit-latn');
>
> 'SELECT' Queries : Same as 'Experiment 1'.
>
> Expectation : All digits should come before all alphabets.
>
> Need help in confirming why 'Experiment 1' is behaving as mentioned
> above -
> 1. If our expectation of 'Experiment 1' is wrong?

I am not sure.
but for the 'Experiment 1', I can use the following collation to get
the expected result you want, I think.
CREATE COLLATION x (provider = icu, locale = 'und-u-latn-digit');

------both two SELECTS, numeric first then alphabets
SELECT * FROM test_table ORDER BY value1 COLLATE x, value2 COLLATE x;
SELECT * FROM test_table ORDER BY value2 COLLATE x, value1 COLLATE x;

Thanks for your response!

Yeah, we can get the correct result with below create collation as well:

CREATE COLLATION test_coll (provider = icu,locale = 'ja',

rules = $$& a <*AbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsStTuUvVwWxXyYzZ <*0-9$$);

 

That is by explicitly giving the priority rule. But we are curious to know

the root cause of 'Experiment 1' behaviour.

I will wait for any response for the same.

Regards,

Nishant.