Text sort problem???

Hi too all,

I just noticed a problem at my database.

I run a query like this:
SELECT name FROM name_tbl ORDER BY name;

In the name column I have names like:
1 - "name1"
2 - "name2"
3 - " unknown";

I use " unknown" with a space at the begine so that the query returnes it as first result. ( in order 3 1 2).

I don't now what happened but the database does not sort anymore these values right(returnes 1-2-3). I noticed this today.
The sort function ignores the "special characters in the sort function".

Does anyone know why? and how to correct this?

I also have another dbserver on another machine, and on that runs well.


Thanks in advance.
Andy.

it's not really ignoring the special characters, the backend turns the string into ASCII and sorts by the ASCII code for each character. The space character, ASCII 32 I think?? is lower than any alphabet character, this is what you're seeing. Perhaps however your data is entered, you could get rid of the spaces on that end.

I looked more carefully and I noticed that it does somehow a "case insensitive" sort instead of a normal sort. Lot of words with 'a...' are after 'A...'.

I don't know what happened. Are there any settings for "case insensitive" sort?

ok, this is because A and a have different ASCII number values. A is 65, a is 97. 1 is 49. So the ordering would be 1, A, a. Does that sort of make sense? If you're using group by, order by sort then it will always use the ASCII number, not what you would always expect. your best bet is to clean the data before it is entered of special characters, or if you know it will happen with things like 1, 2, and 10 (will sort to 1, 10, 2) then you will have to do some more processing, either with your query (put it in a plpgsql procedure), or with the application performing the query and sort.

Hmm, not sure if you just posted this to the Postgres mailing list but here are some possibly pertinent answers:



To which Tom Lane responded


HTH,
-b

My problem was something like this:

I give an example:

These are the datas from the table names:

id n1 n2
1 AaAa AaAa
2 X X
3 A A
4 a a
5 ab ab
6 _Y _Y
8 ..a ..a
9 .x .x
7 ...a ...a

If I run: select * from names order by n1, the result is:

id n1 n2
4 a a
8 ..a ..a
7 ...a ...a
3 A A
1 AaAa AaAa
5 ab ab
9 .x .x
2 X X
6 _Y _Y

(n1 is char(20) n2 is varchar(20)).. somehow the same.


Still now I don't know what wass the cause.

I reinstalled the db. and now works okay. I don't know if this was the only solution.

Have to search the log files. If I found something I will post it.

Best regards.
Andy.