Re: varchar sort ordering ignore blanks

Hello,

you have to use correct localses for your encoding and country:

for czech and utf8 is

cs_CZ.UTF8 ..

for latin2 is

cs_CZ.latin2 etc

czech sorting has more exception and it works

caa
čaa
daa
cha ... it is well for czech
iaa

On 20/01/2008, Luca Arzeni <l(dot)arzeni(at)amadego(dot)com> wrote:
> On Jan 15, 2008 6:37 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> >>Luca Arzeni <l(dot)arzeni(at)amadego(dot)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
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>