Postgres编码问题

安装postgresql时提示选择编码类型，记不太清楚选的是zh_CN还是utf-8，甚至就locale没选。

当我们导入中文时，无法创建。于是有了如下方案：

 

由于好像不能在已有的数据源上更改编码，就只好创建新的data

[root@localhost ~]# mkdir /opt/PostgreSQL/8.3/hidata
[root@localhost ~]# chown postgres /opt/PostgreSQL/8.3/hidata/

su postgres

 

./initdb -E UTF-8 -D /opt/PostgreSQL/8.3/hidata/

(用UTF-8时好像显示的是：)

 

The database cluster will be initialized with locale en_US.UTF-8.
The default text search configuration will be set to "english".

**********************************************

 

当导入空间数据时，也会出错。。。。。。。

 

 

/opt/PostgreSQL/8.3/bin/shp2pgsql /tmp/dhguo/SDB  public.sdb > /tmp/sdb8.sql

/opt/PostgreSQL/8.3/bin/psql -d spatial_DB -f  /tmp/sdb8.sql

 

显示如下错误：

 

psql:/tmp/sdb8.sql:16: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sdb8_pkey" for table "sdb8"
CREATE TABLE
               addgeometrycolumn               
------------------------------------------------
 public.sdb8.the_geom SRID:-1 TYPE:POINT DIMS:2
 
(1 row)

psql:/tmp/sdb8.sql:18: ERROR:  invalid byte sequence for encoding "UTF8": 0xb9
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
psql:/tmp/sdb8.sql:19: ERROR:  invalid byte sequence for encoding "UTF8": 0xc9cf
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
psql:/tmp/sdb8.sql:20: ERROR:  invalid byte sequence for encoding "UTF8": 0xb3
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".

解决办法：

 

 /opt/Postgre  -W GBK /tmp/dhguo/SDB  public.sdb > /tmp/sdb8.sql
/opt/PostgreSQL/8.3/bin/psql -d spatial_DB -f  /tmp/sdb8.sql

 

如果还有错误请检查生成的sdb8.sql对否？我们发现当sql本身有问题时，其不会执行成功。也可以把生成的sdb8.sql的语句拷出来，然后在一些客户端如sql manager 2007上执行。。。