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