On Jan 15, 2008 6:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>Luca Arzeni <l.arzeni@amadego.com> writes:
>> That is: the sort order in postgres 8.1.9 seems to ignore the blank.
>--------------------
>This is expected behavior in most non-C locales.
>Try "initdb --locale=C".
> regards, tom lane
>--------------------
>I guess this has nothing to do with the encoding, but with
>the collation rules used, which is governed by "lc_collate"
>parameter. See what you get on both DBs for:
>SHOW lc_collate ;
>HTH,
>Csaba.
Thanks Tom, and Csaba
both of you hit the problem: actually Postgres 7.4.7 has a C locale and
Postgres 8.1 has US.UTF8 locale. Setting locale to locale=C or
locale=POSIX for release 8.1 solved this issue, but it opens another one:
if I use locale=C, I get:
XXXX A
XXXX C
XXXXB
as sort order (which is fine regarding blanks), but this setting gives me
an error when it cames to:
XXXX d
XXXX e
XXXX f
XXXX è
because the right sort ordering should be:
XXXX d
XXXX e
XXXX è
XXXX f
So the problem is:
- C or POSIX locale is OK with blanks but fails on locale specific vowels
- LATIN9 locale is OK with vowels but ignores blanks
Is there any way to consider blanks meaningfull AND sort properly locale
specific vowels ?
I don't know what SQL standard says about this issue, but I'm sure that in
Italy you sort names considering vowels AND blanks :-} !
Thanks, Luca