Hi, everyone. can i ask a question for th e server side encoding GB18030 and sort order? - Mailing list pgsql-general

Hi,

 

I have set up a postgreSQL on a x86 Solaris 10 box with compiling the source code 8.1.4. My application is need to store the characters encoding with GB18030 or GBK. My Solaris box is installed the locale code zh_CN.GB18030 but I fail to initdb -E GB18030 so I have to use the EUC_CN to initdb and create database.

 

I have published my application since a month and I notice the appcation server report a few jdbc errors that says :

org.postgresql.util.PSQLException: ERROR: character 0xe28094 of encoding "UTF8" has no equivalent in "EUC_CN"

I think the application user input some kind of characters out of range EUC_CN.

 

I change my developing test db server to UTF-8 use the initdb --encoding=UTF-8 and recreate the testing database with encoding utf-8. And this testing db server is host on a windows box the encoding is 936(GBK). I restore the data from the pg_dump file. It's fine to work and the sort order is fine also. I mean the Chinese GB18030 data column is "order by" correctly. But when i do the same thing on the Solaris box. I found the sort order is worng. I can fix this with a convert function like this: select cname from t_resume order by convert(cname using utf8_to_gb18030); on the Solaris box. After I use the convert function the order is correct for the Chinese characters.

 

I think I can change all of my sql in the application to fit this behavior. But when i run the same sql on the windows box. I get the error message: ERROR:  could not convert string to UTF-16: error 1113. I stick on it!

 

I dig on the web and this mail archives but nothing helps. I take a look of the 8.2 beta document in the section "Character Set Support". And I find the table of "Server Character Sets" add a new column Server Support, but the GBK and GB18030 is "not support".

 

So, how can I fix this issue? any ideas? Thank you everyone.