I'm seeing something (very) unexpected with ORDER BY. If I run this query:
select txt
from ( values('x12345'), ('xz1234'), ('x23456'), ('xz2345'), ('x34567'), ('xz3456') ) a(txt)
order by txt;
I get expected results with x<#> being sorted before xz. However, if I replace the z's with ~, giving
select txt
from ( values('x12345'), ('x~1234'), ('x23456'), ('x~2345'), ('x34567'), ('x~3456') ) a(txt)
order by txt;
I get this???
txt
--------
x~1234
x12345
x~2345
x23456
x~3456
x34567
Which appears to mean that ~ is treated differently than z (basically ~ is ignored). Same if I use other special characters, such as @.
Up until stumbling into this, I have never seen such behavior from a database. (Windows OS, yes, but I won't go there...) Character-based text always sorted in an alphabetic order (which puts special characters in different places in the ordering depending on encoding, but it's consistent).
Two questions (which may be the same way of asking the same question):
- How is this correct? I can see where this could be useful in limited scenarios, but IMHO it makes no sense as a default sort order.
- What do I need to do to get a strictly character-based sort in ORDER BY?
I am using postgres version 14.3 on Fedora 37.
Thanks for the help.
Carl
