Numeric collation sorts by digits instead of value- postgres
4
votes
2
answers
896
views
I have a table of users with ids in the following form
user123@domain.com
.
When searching and sorting the users, I need user1@domian.com
to be before user14@domain.com
but since 4 is “smaller” than @ it sorts the other way around.
After looking around I came across the following collation:
CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');
Which fixed the problem, but caused another one, users with numbers that started with 2 appeared after users that started with 1
For example, user19@domain.com
came before user2@domain.com
From the docs, it says that kn-true
and looks at the number’s value but it looks like it only compares the digits.
I'm using Postgres 11.6. I have two servers: one on Windows (local) and a remote one on Linux.
Is anyone familiar with this issue?
Asked by Amit Keinan
(41 rep)
Dec 28, 2022, 07:17 PM
Last activity: Aug 9, 2025, 01:56 PM
Last activity: Aug 9, 2025, 01:56 PM