relative position of digits and ':' in postgres collating sequence

Question

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'

Lucas Hendren · Accepted Answer · 2017-08-18 03:32:14Z

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

edited Aug 18, 2017 at 3:32

answered Aug 18, 2017 at 0:01

Lucas Hendren

2,8522 gold badges20 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pm100 Over a year ago

"collation "sql_latin1_general_cp850_bin" for encoding "UTF8" does not exist", I assume because that is a MySQL doc u pointed me at

Lucas Hendren Over a year ago

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 Over a year ago

If that doesnt work i just found a similar issue involving spaces where they by cast the object to byte array for sorting. stackoverflow.com/questions/34537982/…

Collectives™ on Stack Overflow

relative position of digits and ':' in postgres collating sequence

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related