How does postgres support locale sort and how to adapt with dynamic sorting in Java application?

[former_member204192]

Hi guys,

I create a postgres instance naming "postgres-mydb" on SCP CF enviroment and bind it to my java application. When I create the db instance, I didn't assign Collation and the Character type for it. After my db is created I see that the lc_collate is "en_US.UTF-8" and the encoding is utf8 (I thinks this is from the OS system). So when I execute a sql to get sorted items with "order by" term, PostgreSQL will use the en_US collation, however this sort results seems weird for me. For example, the sorted string ["abc", "#abc", "~abc", "abc co"] returned separates string "abc" and "abc co". So I have to do the query with Collate key word: Select * from myTable order by name COLLATE "C" , here then comes my question:

1. In my java application, I'm using spring data jpa and querydsl to implement dynamic sorting, however, when I try to add collate in my order query, I find out that JPQL doesn't support "COLLATE", I can only use native query to support "COLLATE", however if then It's difficult to implement the dynamic sorting. Do you guys have the same question using postgres? How to implement locale sorting and dynamic sorting(currenty I'm using query dsl) in the same time?

2. My second question is that how commonly the sorting for different locale implemented? Seems we can' t rely on the database sorting, because that when init db, the LC_COLLATE and LC_CTYPE are decided, and these two categories can't be changed anymore. Even though we can assign a collation when writing query sql, however the collation can be used is also limited since the collation used for query has to be compatible with the server encoding I think. For this reason, how does Postgres support for all locales?

And meanwhile should we count on backend sorting? If the data in my application which needs sorting contains English, Chinese, Japanese, Is it possible we sort data in multi locales?

Appreciate it very much If anyone can offer some suggestion, information for help.

Thanks,

Vicky Chen

[former_member532138]

On sap cloud platform, I find that there are several more predefined collations besides "default", "C", "POSIX":

However on my local Window host, I only find "default", "C", "POSIX" these 3 predefined collations.

As I know, the collations may be derived from the operating system. See here. But in the predefined collation list, I don't find collation support for Chinese language. Anybody know how to initialize the collation to support Chinese characters for postgresql on SCP?

thanks,

Bruce.