Strange results of ORDER BY clause when item begins with slash or backslash - Mailing list pgsql-general

I am seeing some unexpected results for an ORDER BY in a query.   It looks
to me as if the sorting is confused about how to handle the slash or
backslash character in a string.  It acts as if ignoring it. Here is a
sample:


          Table "public.test_table"
 Column  |          Type          | Modifiers
---------+------------------------+-----------
 item1   | character varying(500) |
 numitem | integer                |


testdb1=> select * from test_table order by upper(item1);
      item1      | numitem
-----------------+---------
 aaaaa           |     123
 bbbbb           |     234
 test            |     666
 \test\item1     |     555
 total info      |     876
 userdir example |     787
 /usr/otherdir   |     999
 variation       |     777
 \var\overland   |     444
 /var/somedir    |     888
(10 rows)

testdb1=> show LC_COLLATE;
 lc_collate
-------------
 en_US.UTF-8
(1 row)

I would have expected all the items beginning with a backslash to sort
together, and not be interspersed like this.  Can anyone advise on how I
can avoid this?   I would just reorder the data myself, but I am using
OFFSET and LIMIT, and think this will cause problems if the database does
not handle the sorting.

The backslashes were all properly escaped before insertion.

Any ideas appreciated.

Thanks,
Susan C.





----------------------------------------------------------------------------------------------
See our award-winning line of tape and disk-based
backup & recovery solutions at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------