16

I'm trying to change the default value for the client_encoding configuration variable for a PostgreSQL database I'm running. I want it to be UTF8, but currently it's getting set to LATIN1.

The database is already set to use UTF8 encoding:

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 docs should already result in the client using UTF8 as its default client_encoding (emphasis mine):

client_encoding (string)

Sets the client-side encoding (character set). The default is to use the database encoding.

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:

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:

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:

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)
CC BY-SA 3.0

2 Answers 2

Reset to default
23

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:

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.

So, in fact, your previous configuration changes actually have been working, just not in the interactive psql console. Try the following command:

sudo psql --dbname=application_database -c "SHOW client_encoding;" | cat

You should see that the client encoding is actually UTF8:

 client_encoding
-----------------
 UTF8
(1 row)

Now run the command again, but without piping it to 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 LATIN1 encoding for commands involving the terminal.

How can you fix this? Well, there are a few possible ways.

One would be to do as the docs suggest and set the PGCLIENTENCODING environment variable to UTF8 somewhere persistent, such as ~/.profile or ~/.bashrc to affect only your user, or /etc/environment to affect the whole system (see How to permanently set environmental variables).

Another option would be to configure your system locale settings to use en_US.utf8 instead 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/locale or /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.

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):

--command=command

Specifies that psql is to execute one command string, command, and then exit. This is useful in shell scripts. Start-up files (psqlrc and ~/.psqlrc) are ignored with this option.

CC BY-SA 3.0
0
6

Did you set client_encoding in postgresql.conf (and reload config or restart)? Make sure it's UTF8 not utf8

What is the result of cat ~/.psqlrc and cat /etc/psqlrc ?

I know you're looking for server-side default, but on the client, you can set an OS envvar:

export PGCLIENTENCODING=UTF8

to do this for all users (on that machine), put that in /etc/profile

CC BY-SA 3.0
2
  • "What is the result of cat ~/.psqlrc and cat /etc/psqlrc ?" Added to question
    – Ajedi32
    Apr 28, 2016 at 19:08
  • Appending client_encoding = 'UTF8' to /etc/postgresql/9.1/main/postgresql.conf and restarting Postgres with /etc/init.d/postgresql restart had no effect. Client encoding for new connections is still LATIN1. Setting the environment variable did work, but like you said that's specific to the client. Might be "good enough" for my case though...
    – Ajedi32
    Apr 28, 2016 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.