Обсуждение: order by text-type : whitespaces ignored??
I just get my mind crossed here: I do a simple select with order on a text-type. In my opinion entries with leading spaces should be either first (or maybe last) in the list. But in my select the whitespace just seems to be ignored: Note that the second row has a leading space and should imho be first entry. # select traeger from wb_traeger where id>24 order by traeger; traeger ----------------------------------------- GliA - gehirn | lern | impuls | agentur Sonstige Träger Volkshochschule Floridsdorf Zukunftszentrum Tirol (4 rows) note that this not only applies to leading spaces: # select 'x'||traeger from wb_traeger where id>24 order by 'x'||traeger; ?column? ------------------------------------------ xGliA - gehirn | lern | impuls | agentur x Sonstige Träger xVolkshochschule Floridsdorf xZukunftszentrum Tirol (4 rows) the type of my column is simple text: # \d wb_akademie Table "public.wb_akademie" Column | Type | Modifiers -------------+-----------------------------+---------------------------------------------------- <skip> traeger | text | <skip> Is there any way to order so that entries with leading spaces are listed first?? thnx, peter
peter pilsl wrote: > > I just get my mind crossed here: > > I do a simple select with order on a text-type. In my opinion entries > with leading spaces should be either first (or maybe last) in the list. > But in my select the whitespace just seems to be ignored: That'll be down to your locale settings. You'll need to re-initdb to change them I'm afraid. It's locale=C that you want. -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > peter pilsl wrote: >> >> I just get my mind crossed here: >> >> I do a simple select with order on a text-type. In my opinion entries >> with leading spaces should be either first (or maybe last) in the list. >> But in my select the whitespace just seems to be ignored: > > That'll be down to your locale settings. You'll need to re-initdb to > change them I'm afraid. It's locale=C that you want. > thnx - I cant mess up with my locale-setting cause I also need postgres to sort proper german umlauts stored as unicode, which was a difficult task to set up years ago. I had to use de_AT.UTF-8 to get what I want. If I change to locale=C again, I'll sure run into my old problems again: http://www.thescripts.com/forum/thread173467.html (you have participated in that thread also - thnx a lot for your help !!) /usr/local/pgsql8/bin/pg_controldata /data/postgres/postgres8 | grep LC LC_COLLATE: de_AT.UTF-8 LC_CTYPE: de_AT.UTF-8 I just shipped around my current problem by sorting by replace(traeger,' ','0') which of course is not very cool, cause it will end up strange as soon as other entries have 0 in their text, but I cant think of anything else right now. I still find correct sorting a difficult thing to do in postgres :) thnx peter -- mag. peter pilsl - goldfisch.at IT-Consulting Tel: +43-650-3574035 Tel: +43-1-8900602 Fax: +43-1-8900602-15 skype: peter.pilsl pilsl@goldfisch.at www.goldfisch.at