Strange ORDER BY order in PostgreSQL

Question

SELECT vm.value FROM vm ORDER BY vm.value ASC;

results in:

.
0
0%
0.0
0.964
%.0f
%.0f mph
-1.0°
11/1
11-14-1981
112 mph
11:48:43
%1$.1f mph %2$@
1/2
12.5˚
1/4
195 lb
%.1f°
2 days ago
%.2f
2º out-in
3/4
3.5
3.6
3D
3 days ago
4º closed
5'10''
5”10”
Account

I can remember that ASCII/Unicode digits should follow each other tightly not alternated with percents and other characters.

Looks like percents, dots and other special characters are just ignored during comparison. — alno
– alno, Commented Feb 21, 2013 at 19:15
As ordering is governed by your locale, What is your locale set to? Try switching to the C locale and see if that changes it. — Doon
– Doon, Commented Feb 21, 2013 at 19:21
I thought that ORDER BY should be universal, locale-independent as is the Unicode itself. BTW: how to change/view locale? — Paul
– Paul, Commented Feb 21, 2013 at 19:25

Daniel Vérité · Accepted Answer · 2013-02-21 19:27:32Z

6

I can remember that ASCII/Unicode digits should follow each other tightly not alternated with percents and other characters.

This applies to some locales but not others. Obviously it doesn't apply to the one you're using. Issue show lc_collate in SQL to see what locale is in use.

With recent versions of PG, including 9.1, you may use a per-query specific locale to sort, like this:

SELECT vm.value FROM vm ORDER BY vm.value COLLATE "C";

For the specific data shown in the question, this would sort as you expect.

answered Feb 21, 2013 at 19:27

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

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

3 Comments

Joachim Isaksson Over a year ago

en_US.UTF-8 sorts in the manner the question describes. One would think that collation wouldn't disregard non alphanumerics.

Paul Over a year ago

Yes, I am using en_US.UTF-8

Dmitry Mugtasimov Over a year ago

@JoachimIsaksson Where is en_US.UTF-8 collation order documented?

Collectives™ on Stack Overflow

Strange ORDER BY order in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related