0

This is driving me bonkers. My Heroku Postgres (9.5.18) DB seems to be ignoring underscores when sorting results:

Query: 

SELECT category FROM categories ORDER BY category ASC;

Results: 

category
-------------------
z_commercial_overlay
z_district
zr_use_group
zr_uses_footnote
z_special_district

This is new to me. I've never noticed another system where underscores are not respected in sorting, and this is the first time I've noticed Postgres behaving like this.

On my local OSX box (Postgres 10.5) the results are sorted the 'normal' expected way:

category
-------------------
z_commercial_overlay
z_district
z_special_district
zr_use_group
zr_uses_footnote

UPDATE:

Based on the comments, I was able to get the correct sorting by using COLLATE "C"

SELECT category FROM categories ORDER BY category COLLATE "C" ASC;

But I don't understand why is this necessary. BOTH of the Postgres instances show the same default collation value, and all of the table columns were created the same way, with no alternate collation specified.

SHOW lc_collate;

 lc_collate  
-------------
 en_US.UTF-8

SHOW lc_ctype;

  lc_ctype   
-------------
 en_US.UTF-8

So why does the Heroku Postgres DB require the COLLATE declaration?

Yarin
  • 173,523
  • 149
  • 402
  • 512
  • 1
    Check these links: https://dba.stackexchange.com/questions/115364/why-does-postgres-order-by-seem-to-halfway-ignore-leading-underscores and https://stackoverflow.com/questions/22534484/postgresql-incorrect-sorting – forpas Sep 17 '19 at 16:47
  • 1
    [Postgresql order by returning two different orders](https://stackoverflow.com/a/31925591/1995738) – klin Sep 17 '19 at 16:53
  • See my update... – Yarin Sep 17 '19 at 17:20

1 Answers1

0

I've never encountered another system where underscores are not respected in sorting

Really? Never used one, or just never paid attention to one?

On Ubuntu 16.04 (and every other modern system I've paid attention to), the system sort tool behaves the same way as long as you are using en_US.

LC_ALL= LANG=en_US.UTF-8 sort
<produced the same order as the first one you show above)

On my local box (Postgres 10.5) the results are sorted the 'normal' expected way: BOTH of the Postgres instances show the same collation value:

SHOW lc_collate;
lc_collate  

 en_US.UTF-8

That only shows the default collation for the database. The column could have been declared to use a different collation than the default:

create table categories(category text collate "C");

If your local database is supposed to be using en_US, and is not, then it is busted.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • "On Ubuntu 16.04 (and every other modern system I've paid attention to), the system sort tool behaves the same way" - that's strange to me, as it's not the way most common programming languages do it (Ruby, JS) nor my OSX Postgres instance. "That only shows the default collation for the database. The column could have been declared to use a different collation than the default" - that's not what happened- this is a Rails-generated DB I generated locally without specifying collations, and then deployed to Heroku. That's why it doesn't make sense. – Yarin Sep 17 '19 at 20:55