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
----------------------------------------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
