Обсуждение: sorting problem

I found some problem about the sorting when i switch to pg version 8.3.4

Table name : tt,

record list :

      item_code
----------------------
 VAAAA
 VBBBB
 VCCC
 (V)BBBB
 V.BBBB
 V.AAAAB
 V.AAAA
 .VAAAA
 VA.AAA
 (VBBBB
 V)BBBB
 (V)B.BBB
 (VB)BBB
(13 rows)

the result : select * from tt order by item_code;

      item_code
----------------------
 VAAAA
 .VAAAA
 V.AAAA
 VA.AAA
 V.AAAAB
 VBBBB
 (VBBBB
 (V)BBBB
 (V)B.BBB
 (VB)BBB
 V.BBBB
 V)BBBB
 VCCC
(13 rows)

the sort sequence like ignore the character '.' , '(', ')'. Is it a bug ?
It is no problem in old version !!!!

>>> CK Leung <ck@wecity.com> wrote:
> the result : select * from tt order by item_code;
>
>       item_code
> ----------------------
>  VAAAA
>  .VAAAA
>  V.AAAA
>  VA.AAA
>  V.AAAAB
>  VBBBB
>  (VBBBB
>  (V)BBBB
>  (V)B.BBB
>  (VB)BBB
>  V.BBBB
>  V)BBBB
>  VCCC
> (13 rows)
>
> the sort sequence like ignore the character '.' , '(', ')'. Is it a
> bug ?  It is no problem in old version !!!!

Probably not a bug; many collating sequences are defined to ignore
such characters.  Perhaps you chose a different locale when you
converted?

-Kevin

I create a new database use UTF-8 and SQL_ASCII, same result found, how
can i fix this problem ?

CK

Kevin Grittner wrote:
>>>> CK Leung <ck@wecity.com> wrote:
>>>>
>> the result : select * from tt order by item_code;
>>
>>       item_code
>> ----------------------
>>  VAAAA
>>  .VAAAA
>>  V.AAAA
>>  VA.AAA
>>  V.AAAAB
>>  VBBBB
>>  (VBBBB
>>  (V)BBBB
>>  (V)B.BBB
>>  (VB)BBB
>>  V.BBBB
>>  V)BBBB
>>  VCCC
>> (13 rows)
>>
>> the sort sequence like ignore the character '.' , '(', ')'. Is it a
>> bug ?  It is no problem in old version !!!!
>>
>
> Probably not a bug; many collating sequences are defined to ignore
> such characters.  Perhaps you chose a different locale when you
> converted?
>
> -Kevin
>
>

CK Leung wrote:
> I create a new database use UTF-8 and SQL_ASCII, same result found, how
> can i fix this problem ?

If I recall correctly, the collation order is set at initdb time, and
isn't bound to the database encoding. If that's right, you'd need to
dump all your databases, re-initdb with the new collation settings, and
re-load your databases.

--
Craig Ringer

Try setting the variable lc_collate, either in an individual session
using SET or (to change it for all session) in postgresql.conf.

Craig Ringer wrote:

  CK Leung wrote:


    I create a new database use UTF-8 and SQL_ASCII, same result found, how
can i fix this problem ?



If I recall correctly, the collation order is set at initdb time, and
isn't bound to the database encoding. If that's right, you'd need to
dump all your databases, re-initdb with the new collation settings, and
re-load your databases.

--
Craig Ringer