0

say I have a table 't1' with a string column 'name'. And I have names 'n1','n2','n9' and 'n:'. If I do

select * from t1 orderby name asc

I expect

n1
n2
n9
n:

Given that ':' comes after '9' in ASCII, but instead, I get

n:
n1
n2
n9

Which is a surprise. Is there something I need to do to say 'use ASCII as the collating sequence for basic ASCII chars'

pm100
  • 48,078
  • 23
  • 82
  • 145

1 Answers1

1

From my experience, this is a collation issue

SELECT *    FROM t1 ORDER BY name COLLATE "POSIX";

This is a list of exapmle collations in case that collation have listed, SQL_Latin1_General_CP850_BIN does not work

https://www.postgresql.org/docs/9.1/static/collation.html

Lucas Hendren
  • 2,786
  • 2
  • 18
  • 33
  • "collation "sql_latin1_general_cp850_bin" for encoding "UTF8" does not exist", I assume because that is a MySQL doc u pointed me at – pm100 Aug 18 '17 at 00:13
  • Sorry about that, I updated it with a link to the collation page for postgres, it also says "C" should work if but those should also be the default. – Lucas Hendren Aug 18 '17 at 03:37
  • If that doesnt work i just found a similar issue involving spaces where they by cast the object to byte array for sorting. https://stackoverflow.com/questions/34537982/incorrect-sort-collation-order-with-spaces-in-postgresql-9-4 – Lucas Hendren Aug 18 '17 at 03:39