postgresql插入汉字报错

错误信息：peimsmdb=# select '我';ERROR: character with byte sequence 0xe6 0x88 0x91 in encoding "UTF8" has no equivalent in encoding "LATIN1"
查看客户端字符集:peimsmdb=# show client_encoding; client_encoding ----------------- UTF8
peimsmdb=# show server_encoding; server_encoding ----------------- LATIN1
思路：因为没有修改环境变量，导致安装完数据库，数据库默认字符集变成LATIN1，如下所示。
postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+---------+-------+----------------------- peimsmdb | peimsmer | LATIN1 | en_US | en_US | =Tc/peimsmer + | | | | | peimsmer=CTc/peimsmer postgres | postgres | LATIN1 | en_US | en_US | template0 | postgres | LATIN1 | en_US | en_US | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | LATIN1 | en_US | en_US | =c/postgres + | | | | | postgres=CTc/postgres(4 rows)
所以如果一开始修改了环境变量，也不会出现之后的问题。修改环境变量# vi　/etc/profileexport.UTF-8export LC_ALL=en_US.UTF-8export LC_CTYPE=en_US.UTF-8
# source /etc/profile
现在数据库已经安装，解决办法只能是在建库的时候指定字符集。
首先修改环境变量
命令参考：create database DATABASENAME with encoding = 'UTF8' LC_CTYPE = 'en_US.UTF-8' LC_COLLATE = 'en_US.UTF-8' template = template1;
在建库之前首先处理template0和template1两个表。
# psql -U postgres -d postgres -h 127.0.0.1
postgres=# update pg_database set datallowconn = TRUE where datname = 'template0';UPDATE 1postgres=# \c template0You are now connected to database "template0" as user "postgres".template0=# update pg_database set datistemplate = FALSE where datname = 'template1';UPDATE 1template0=# drop database template1;DROP DATABASEtemplate0=# create database template1 with encoding = 'UTF8' LC_CTYPE = 'en_US.UTF-8' LC_COLLATE = 'en_US.UTF-8' template = template0;CREATE DATABASEtemplate0=# update pg_database set datallowconn = TRUE where datname = 'template1';UPDATE 1template0=# \c template1You are now connected to database "template1" as user "postgres".template1=# update pg_database set datallowconn = FALSE where datname = 'template0';UPDATE 1参考：https://blog.csdn.net/hkyw000/article/details/52817422