1

I have a text column in PostgreSQL that I am ordering by in ASC order and it is returning the following order:

SELECT column_A FROM table ORDER BY column_A ASC:

column_A
IKvap06:52:22.52
i.lost.all.my.tacos
IQKPb06:55:51.53

But I was expecting this:

column_A
i.lost.all.my.tacos
IKvap06:52:22.52
IQKPb06:55:51.53

Any ideas as to why/how this happening? Using locale: en_US.UTF8

I double checked the locale in the database instance to make sure it was en_US.UTF8. I can't seem figure out why I am getting this sort.

user21068201
  • 11
  • 2
  • 1
    `order by lower(column_a)` to ignore upper/lower case –  Jan 23 '23 at 21:15
  • @a_horse_with_no_name, That won't work: `select lower('i.lost.all.my.tacos') < lower('IKvap06:52:22.52'); f`. This is down to locale sorting rules. Per: `select lower('i.lost.all.my.tacos') < lower('IKvap06:52:22.52' collate "C"); t`. – Adrian Klaver Jan 23 '23 at 21:23
  • This is definitely weird. https://dbfiddle.uk/SYzS8t_H is a fiddle. – O. Jones Jan 23 '23 at 21:24
  • Worth reading, I guess: https://wiki.postgresql.org/wiki/FAQ#Why_do_my_strings_sort_incorrectly.3F – FiddlingAway Jan 23 '23 at 22:31
  • Why are you expecting what you are expecting? That is not how the default collation for en_US is defined. It is weird to use a specific locale but expect that that locale not be used. – jjanes Jan 23 '23 at 22:33
  • @jjanes My company's database instance is set to that locale. I want understand the sort that I am seeing so that I can replicate is somewhere else. In general, that is what I "would" expect but the main purpose for the question is to understand. – user21068201 Jan 26 '23 at 15:27
  • On the linux command line, I can replicate your shown sort order with `sort` as long as LC_ALL is unset and LANG is set to 'en_US.UTF8'. Your expected sort order I can replicate with LC_ALL=C using `sort -f`. – jjanes Jan 26 '23 at 18:37

0 Answers0