<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<p>I'm seeing something (very) unexpected with ORDER BY. If I run
this query:</p>
<p>select txt<br>
from ( values('x12345'), ('xz1234'), ('x23456'), ('xz2345'),
('x34567'), ('xz3456') ) a(txt)<br>
order by txt;</p>
<p>I get expected results with x<#> being sorted before xz.
However, if I replace the z's with ~, giving</p>
<p>select txt<br>
from ( values('x12345'), ('x~1234'), ('x23456'), ('x~2345'),
('x34567'), ('x~3456') ) a(txt)<br>
order by txt;</p>
<p>I get this???</p>
<p> txt <br>
--------<br>
x~1234<br>
x12345<br>
x~2345<br>
x23456<br>
x~3456<br>
x34567<br>
</p>
<p>Which appears to mean that ~ is treated differently than z
(basically ~ is ignored). Same if I use other special characters,
such as @.</p>
<p>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).</p>
<p>Two questions (which may be the same way of asking the same
question):</p>
<p>- 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.<br>
</p>
<p>- What do I need to do to get a strictly character-based sort in
ORDER BY?</p>
<p>I am using postgres version 14.3 on Fedora 37.<br>
</p>
<p>Thanks for the help.</p>
<p>Carl</p>
<p><br>
</p>
</body>
</html>