Обсуждение: SQL Query Problem
The server is running: PostgreSQL version 7.0.2 RedHat Linux 6.2 When using ORDER BY in an SQL statement where the data type is varchar and the data are unix directory paths, the forward slashes (/) are ignored, causing the results to be returned in the incorrect order. Example: Here is the INPUT: /usr/local/data/x/d /usr/local/data/xc/e /usr/local/data/x/a /usr/local/data/xc /usr/local/data/x/b Here is the QUERY: SELECT * FROM mytable ORDER BY path Here is the OUTPUT: /usr/local/data/x/a /usr/local/data/x/b /usr/local/data/xc /usr/local/data/xc/e /usr/local/data/x/d Note that the program appears to make no distinction between /x/c and /xc. I could be wrong, but I believe that the output order should be as follows: Here is the OUTPUT I expected: /usr/local/data/x/a /usr/local/data/x/b /usr/local/data/x/d /usr/local/data/xc /usr/local/data/xc/e ---------- Kristian T. Lance Database Application Developer (IS/Web) Client Support Services Information Networks and Systems Communications Research Centre Canada 3701 Carling Avenue Ottawa, ON K2H 8S2 Tel.: (613) 991-6812 Fax: (613) 991-5795 http://www.crc.ca
On Thu, 29 Nov 2001, Kristian Lance wrote: > The server is running: > > PostgreSQL version 7.0.2 > RedHat Linux 6.2 > > When using ORDER BY in an SQL statement where the data type is varchar and > the data are unix directory paths, the forward slashes (/) are ignored, > causing the results to be returned in the incorrect order. Are you running with locale enabled and what are the LC_* settings you are starting the server with?
Kristian Lance <kristian.lance@crc.ca> writes: > Note that the program appears to make no distinction between /x/c and > /xc. Sounds like you're running in en_US locale. You might prefer to use plain C locale. regards, tom lane