Postgres issue encoding "UTF8" has no equivalent in encoding "LATIN1"

Question

Our postgres production database server has a database called crd_production which is born out of the template1 template 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 template1 db and created it afresh with an utf-8 encoding as you can see below.

      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_production db as the primary database. There is no issue when ActiveRecord is writing/reading data but when I try to fire any sql query from psql command line on this db, the following error occurs -

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?

Platform

Answer 1

As guessed, the problem was with the client_encoding on the database.

crd_production=# show client_encoding;
 client_encoding 
-----------------
 LATIN1
(1 row)

To change the client encoding to UTF-8, you need to do this

crd_production=#  SET client_encoding = 'UTF8';
SET

Check again

crd_production=# show client_encoding;
 client_encoding 
-----------------
 UTF8
(1 row)

Things work fine now.

Platform

See if you can do an alter user set client_encoding='UTF8'; to make this sticky from now on. Or an alter database if you'd rather. – Threat

I have the same error, but when I tried to check the client_encoding, it was showing me UNICODE instead of LATIN1. Hmmm. – Vernice

@ScottMarlowe FYI, that doesn't seem to be working for me: https://mcmap.net/q/452612/-how-do-i-change-the-default-client_encoding-in-postgres/1157054 – Koeninger

Hi, I am a bit confused about this client_encoding and server_encoding, can anyone explain the difference ? – Frazzled

Answer 2

I have same case before with ruby on rails on postgresql 10. This is the trick

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?

Punch

Worth making a note that this approach actually changes the encoding of the database, rather than just your (local?) client. That may cause issues for other people using the database and assuming another encoding! Unless you're sure this is OK, change the client as papdel's answer suggests. – Spoke