Search Postgresql Archives

Re: varchar sort ordering ignore blanks

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Jan 15, 2008 6:37 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

  >>Luca Arzeni <l.arzeni@xxxxxxxxxxx> 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/

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux