Lists: | pgsql-bugs |
---|
From: | Kristian Lance <kristian(dot)lance(at)crc(dot)ca> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | SQL Query Problem |
Date: | 2001-11-29 18:06:51 |
Message-ID: | 5.1.0.14.2.20011128141019.01da6cc8@mail.crc.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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
From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Kristian Lance <kristian(dot)lance(at)crc(dot)ca> |
Cc: | <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: SQL Query Problem |
Date: | 2001-12-03 18:28:00 |
Message-ID: | 20011203102702.O93342-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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?
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Kristian Lance <kristian(dot)lance(at)crc(dot)ca> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: SQL Query Problem |
Date: | 2001-12-03 18:28:35 |
Message-ID: | 17846.1007404115@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Kristian Lance <kristian(dot)lance(at)crc(dot)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