[SOLVED] Postgresql - Incorrect Sorting

Issue

I have a leads_lead table in the database and when I use the following query I get the results as shown below.

Query

select name 
from leads_lead as ll 
where ll.branch_id = 14 
order by ll.name desc;

Results

           name            
---------------------------
 testing app 7
 testing app 4
 testing app 22
 testing app 22
 testing app 112
 testing app 10
 testing 9
 testing 6
 testing 5
 testing 3
 testing 2
 Usha Devi Kewat
 Uma kumari tharu
 Tulsiram Yadav
 Triveni Chaudhary
 Testing From APP
 Testing
 Testing
 Testing
 Test Time
 .....
 .....

But the sorting is not correct. Is this some bug in PostgreSQL? I'm using Postgres 13.3 in Ubuntu server 20.04.1 and encoding is UTF8.

Solution

You seem to have your database created with the C collation, which sorts according to code point.

But you can specify other collations, for example

ORDER BY ll.name COLLATE "en_US" DESC

You have to use a collation that is defined in your database, see pg_collation for the list.

Answered By - Laurenz Albe

Answer Checked By - David Marino (WPSolving Volunteer)