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.

Answer 1

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.