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?
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.