Strange results of ORDER BY clause when item begins with slash or backslash

From: SCassidy(at)overlandstorage(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: Strange results of ORDER BY clause when item begins with slash or backslash
Date: 2005-01-19 19:24:12
Message-ID: OFF62B7774.11F97949-ON88256F8E.0068C1BD-88256F8E.006A95FB@myoverland.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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
----------------------------------------------------------------------------------------------

Responses

Browse pgsql-general by date

  From Date Subject
Next Message A. Mous 2005-01-19 19:24:40 Re: PostgreSQL 8.0.0 Released
Previous Message Guy Rouillier 2005-01-19 19:19:16 Re: Best Linux Distribution