Sample Header Ad - 728x90

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