Why ruby sort array of strings differently than sql order (postgres)?

Question

When the code would tell better than words:

[4] pry> Models::Company.order(:name).select_map(:name)
=> ["Dekker, Jacobs and Bosch", "Koster en Zonen", "Peters-Koster", "Peters, Vries and Smits", "Wal, Linden and Jacobs"]
[5] pry> Models::Company.order(:name).select_map(:name).sort
=> ["Dekker, Jacobs and Bosch", "Koster en Zonen", "Peters, Vries and Smits", "Peters-Koster", "Wal, Linden and Jacobs"]

Because of this my spec randomly failing. I can normalize arrays before comparing them, like this:

result_names.map { |s| s.gsub(/,|-/, '') }

I tried to sort the same array in JavaScript, and got the same result as Ruby gave me. I am wondering now, is there some sort of standard for sorting strings, or is it the detail of implementation?

Something to do with encoding or collation configuration of your Postgres server, perhaps? — Tom Lord
– Tom Lord, Commented May 30, 2017 at 16:40
@DavidC Unfortunately, the environment has changed and I can't retrieve the same results to answer precisely. But I've tried to insert into db the same data that I've posted here, and I've got the same order that time. So I suppose this is because of unicode dashes? It seems that Ruby normalizes such strings then. — Danny Ocean
– Danny Ocean, Commented May 30, 2017 at 17:27

idej · Accepted Answer · 2017-05-30 21:18:20Z

3

Sort behaviour for strings in ruby based on ASCII codes.

Sort behaviour for text in pg depends on the current collation of your locale. From PostgreSQL wiki - Why do my strings sort incorrectly?:

It is not in ASCII/byte order. No, it's not, it's not supposed to be. ASCII is an encoding, not a sort order. If you want this, you can use the C locale, but then you use the ability to non-ASCII characters.

So in plain SQL to sort by ASCII value, rather than a properly localized sort following your local language rules, you can use the COLLATE clause in query

order by name COLLATE "C" ASC

You can check your collate settings in psql with SHOW lc_collate;.

PostgreSQL uses OS collation support, so it's possible for results to vary slightly from host OS to host OS. Some versions of Mac OS X or a BSD-family operating system have problems with locale definitions.

edited May 30, 2017 at 21:18

answered May 30, 2017 at 21:12

idej

5,9441 gold badge14 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

Why ruby sort array of strings differently than sql order (postgres)?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related