如何查看配置为LATIN1编码的PostgreSQL数据库中的数据

错误原因 #

数据库配置为LATIN1编码，但实际存储的数据中包含了UTF8编码的字符（报错中的0xe2 0x80 0x9c是UTF8格式的左双引号）。当客户端以LATIN1编码连接时，PostgreSQL无法将这些UTF8字节转换为LATIN1兼容字符，因此抛出转换错误。

可行解决方案 #

临时切换客户端编码为UTF8（快速查看） #

直接在当前会话中修改客户端编码为UTF8，兼容数据中的UTF8字符，无需修改数据库：

SET client_encoding = 'UTF8';
SELECT foo FROM bar;

此设置仅在当前会话生效，退出后恢复原编码。

永久转换数据库编码为UTF8（彻底解决） #

如果业务允许，将数据库编码切换为UTF8是长期解决方案，步骤如下：

1. 备份数据库：

   pg_dump -d your_database_name -f db_backup.sql
   

2. 终止数据库的所有连接后，删除原数据库：

   DROP DATABASE your_database_name;
   

3. 创建UTF8编码的新数据库：

   CREATE DATABASE your_database_name ENCODING 'UTF8';
   

4. 恢复备份数据到新数据库：

   psql -d your_database_name -f db_backup.sql
   

查询时转换字符（保留原编码，跳过/替换无效字符） #

如果无法修改数据库或客户端编码，可在查询时通过convert()函数转换字符，将无法兼容的字符替换为指定符号（如问号）：

SELECT convert(foo::bytea, 'UTF8', 'LATIN1', 'replace') FROM bar;

此方法会丢失部分无法转换的字符信息，但能正常返回查询结果。

内容的提问来源于stack exchange，提问作者user352290