postgresql 如何更改 Postgres 中的默认 client_encoding？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36922248/
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
How do I change the default client_encoding in Postgres?
提问by Ajedi32
I'm trying to change the default value for the client_encodingconfiguration variable for a PostgreSQL database I'm running. I want it to be UTF8, but currently it's getting set to LATIN1.
我正在尝试更改client_encoding我正在运行的 PostgreSQL 数据库的配置变量的默认值。我希望它是UTF8，但目前它正在设置为LATIN1。
The database is already set to use UTF8 encoding:
数据库已设置为使用 UTF8 编码：
application_database=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
----------------------+----------+----------+-------------+-------------+--------------------------------------
postgres | postgres | LATIN1 | en_US | en_US |
application_database | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres +
| | | | | application_database=Tc/postgres
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)
Which according to the docsshould alreadyresult in the client using UTF8 as its default client_encoding(emphasis mine):
其中根据文档应该已经导致使用UTF-8作为其默认的客户端client_encoding（重点煤矿）：
client_encoding (string)Sets the client-side encoding (character set). The default is to use the database encoding.
client_encoding (string)设置客户端编码（字符集）。默认是使用数据库编码。
But it doesn't:
但它没有：
$ sudo psql --dbname=application_database
psql (9.1.19)
Type "help" for help.
application_database=# SHOW client_encoding;
client_encoding
-----------------
LATIN1
(1 row)
I even tried using ALTER USER <user> SET ...to change the default config for the user I'm logging in as:
我什至尝试使用ALTER USER <user> SET ...更改我登录的用户的默认配置：
application_database=# ALTER USER root SET client_encoding='UTF8';
ALTER ROLE
application_database=# SELECT usename, useconfig FROM pg_shadow;
usename | useconfig
----------------------+------------------------
postgres |
root | {client_encoding=UTF8}
application_database |
(3 rows)
But that also had no effect:
但这也没有效果：
$ sudo psql --dbname=application_database
psql (9.1.19)
Type "help" for help.
application_database=# SELECT current_user;
current_user
--------------
root
(1 row)
application_database=# SHOW client_encoding;
client_encoding
-----------------
LATIN1
(1 row)
There's nothing in any of the PSQL files on my system:
我系统上的任何 PSQL 文件中都没有任何内容：
vagrant@app-database:~$ cat ~/.psqlrc
cat: /home/vagrant/.psqlrc: No such file or directory
vagrant@app-database:~$ cat /etc/psqlrc
cat: /etc/psqlrc: No such file or directory
vagrant@app-database:~$ sudo su
root@app-database:/home/vagrant# cat ~/.psqlrc
cat: /root/.psqlrc: No such file or directory
I'm running PosgreSQL 9.1:
我正在运行 PosgreSQL 9.1：
application_database=# SELECT version();
version
-------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.19 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
(1 row)
回答by Ajedi32
Okay, so first things first: why isn't setting the user or database encoding having any effect?
好的，首先要做的是：为什么设置用户或数据库编码没有任何影响？
Turns out it's because of this line from the psql documentation:
原来这是因为psql 文档中的这一行：
If at least one of standard input or standard output are a terminal, then psql sets the client encoding to "auto", which will detect the appropriate client encoding from the locale settings (LC_CTYPE environment variable on Unix systems). If this doesn't work out as expected, the client encoding can be overridden using the environment variable PGCLIENTENCODING.
如果标准输入或标准输出中至少有一个是终端，则 psql 将客户端编码设置为“auto”，这将从区域设置（Unix 系统上的 LC_CTYPE 环境变量）中检测适当的客户端编码。如果这没有按预期工作，则可以使用环境变量 PGCLIENTENCODING 覆盖客户端编码。
So, in fact, your previous configuration changes actually havebeen working, just not in the interactive psqlconsole. Try the following command:
所以，事实上，您之前的配置更改实际上一直在起作用，只是不在交互式psql控制台中。尝试以下命令：
sudo psql --dbname=application_database -c "SHOW client_encoding;" | cat
You should see that the client encoding is actually UTF8:
您应该看到客户端编码实际上是 UTF8：
client_encoding
-----------------
UTF8
(1 row)
Now run the command again, but without piping it to cat:
现在再次运行该命令，但不要将其通过管道传输到cat：
sudo psql --dbname=application_database -c "SHOW client_encoding;"
You should get the result:
你应该得到结果：
client_encoding
-----------------
LATIN1
(1 row)
So basically, psql is only using LATIN1encoding for commands involving the terminal.
所以基本上，psql 只LATIN1对涉及终端的命令使用编码。
How can you fix this? Well, there are a few possible ways.
你怎么能解决这个问题？嗯，有几种可能的方法。
One would be to do as the docs suggest and set the PGCLIENTENCODINGenvironment variable to UTF8somewhere persistent, such as ~/.profileor ~/.bashrcto affect only your user, or /etc/environmentto affect the whole system (see How to permanently set environmental variables).
一种方法是按照文档的建议将PGCLIENTENCODING环境变量设置为UTF8某个持久的地方，例如~/.profile或~/.bashrc仅影响您的用户，或/etc/environment影响整个系统（请参阅如何永久设置环境变量）。
Another option would be to configure your system locale settings to use en_US.utf8instead of en_US(or equivalent). The method for doing this may vary depending on your system, but usually you can do it by modifying ~/.config/locale.conf(your user only) or /etc/default/localeor /etc/locale.conf(system-wide). This will affect more than just postgres, and I believe more closely addresses the root of the problem. You can check your current locale settings by running locale.
另一种选择是配置您的系统区域设置以使用en_US.utf8而不是en_US（或等效的）。执行此操作的方法可能因您的系统而异，但通常您可以通过修改~/.config/locale.conf（仅限您的用户）或/etc/default/locale或/etc/locale.conf（系统范围）来实现。这不仅会影响 postgres，而且我相信更接近于解决问题的根源。您可以通过运行来检查您当前的区域设置locale。
One other solution would be to update your psqlrc file to include SET client_encoding=UTF8;. That file is located at ~/.psqlrc(your user only) or /etc/psqlrc(system-wide). Note that this method won't affect the result of the command we were using to the client encoding earlier, since the docs state(emphasis mine):
另一种解决方案是更新您的 psqlrc 文件以包含SET client_encoding=UTF8;. 该文件位于~/.psqlrc（仅限您的用户）或/etc/psqlrc（系统范围内）。请注意，此方法不会影响我们之前用于客户端编码的命令的结果，因为文档状态（重点是我的）：
--command=commandSpecifies that psql is to execute one command string, command, and then exit. This is useful in shell scripts. Start-up files (
psqlrcand~/.psqlrc) are ignored with this option.
--command=command指定 psql 执行一个命令串，command，然后退出。这在 shell 脚本中很有用。此选项将忽略启动文件（
psqlrc和~/.psqlrc）。
回答by Neil McGuigan
Did you set client_encodingin postgresql.conf(and reload config or restart)? Make sure it's UTF8 not utf8
你设置client_encoding的postgresql.conf（和重新加载配置或重新启动）？确保它是 UTF8 而不是 utf8
What is the result of cat ~/.psqlrcand cat /etc/psqlrc?
cat ~/.psqlrc和的结果是cat /etc/psqlrc什么？
I know you're looking for server-side default, but on the client, you can set an OS envvar:
我知道您正在寻找服务器端默认值，但在客户端上，您可以设置操作系统 envvar：
export PGCLIENTENCODING=UTF8
export PGCLIENTENCODING=UTF8
to do this for all users (on that machine), put that in /etc/profile
要为所有用户（在该机器上）执行此操作，请将其放入 /etc/profile
