Обсуждение: SQL Query Problem

Поиск
Список
Период
Сортировка

SQL Query Problem

От
Kristian Lance
Дата:
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

Re: SQL Query Problem

От
Stephan Szabo
Дата:
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?

Re: SQL Query Problem

От
Tom Lane
Дата:
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