postgresql Postgres 问题编码“UTF8”在编码“LATIN1”方面没有等价物
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14525505/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres issue encoding "UTF8" has no equivalent in encoding "LATIN1"
提问by papdel
Our postgres production database server has a database called crd_production which is born out of the template1template database. Incidentally, on an Ubuntu 12.04 box, the default encoding of the template1 and template0 databases on initial creation of the pgcluster had a default encoding of LATIN1. I had dropped the template1db and created it afresh with an utf-8 encoding as you can see below.
我们的 postgres 生产数据库服务器有一个名为 crd_production 的数据库，它是从template1模板数据库中诞生的。顺便说一句，在 Ubuntu 12.04 机器上，初始创建 pgcluster 时 template1 和 template0 数据库的默认编码具有 LATIN1 的默认编码。我已经删除了template1数据库并使用 utf-8 编码重新创建它，如下所示。
Name | Owner | Encoding | Collate | Ctype | Access privileges
----------------+----------+----------+------------+------------+-----------------------
crd_production | deployer | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | LATIN1 | en_US | en_US | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
(4 rows)
We eventually deployed our rails(3.2.11) app and started using the crd_productiondb as the primary database. There is no issue when ActiveRecord is writing/reading data but when I try to fire any sql query from psqlcommand line on this db, the following error occurs -
我们最终部署了我们的 rails(3.2.11) 应用程序并开始使用crd_productiondb 作为主数据库。ActiveRecord 写入/读取数据时没有问题，但是当我尝试从psql该数据库的命令行中触发任何 sql 查询时，会发生以下错误 -
crd_production=# select * from users;
ERROR: character with byte sequence 0xe2 0x80 0x9c in encoding "UTF8" has no equivalent in encoding "LATIN1"
crd_production=# select * from features;
ERROR: character with byte sequence 0xe2 0x80 0x99 in encoding "UTF8" has no equivalent in encoding "LATIN1"
What could be the problem here? Is it an issue with the client?
这里可能有什么问题？是客户端的问题吗？
回答by papdel
As guessed, the problem was with the client_encoding on the database.
正如猜测的那样，问题出在数据库上的 client_encoding 上。
crd_production=# show client_encoding;
client_encoding
-----------------
LATIN1
(1 row)
To change the client encoding to UTF-8, you need to do this
要将客户端编码更改为 UTF-8，您需要这样做
crd_production=# SET client_encoding = 'UTF8';
SET
Check again
再检查一遍
crd_production=# show client_encoding;
client_encoding
-----------------
UTF8
(1 row)
Things work fine now.
现在一切正常。
回答by Mada Aryakusumah
I have same case before with ruby on rails on postgresql 10. This is the trick
我之前在 postgresql 10 上使用 ruby on rails 也有过同样的情况。这就是窍门
update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'thedb'
Source : How do you change the character encoding of a postgres database?
