Обсуждение: 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