PostgreSQL Japanese characters encoding issue

Question

for some reason japanese characters are not being stores correctly in my database, on shell is coming up as x1a and in pgadmin just squares.

I found out after I was getting constraint violation when inserting records, and then I removed the constrain to let the values be stored and realise the values were not being encoded properly.

13:14:05 - PGS-220000 PostgreSQL error: ERROR: duplicate key value violates unique constraint "nmscountry_labelja" DETAIL: Key (slabelja)=(¿¿¿¿¿) already exists. . 13:14:05 - WDB-200001 SQL statement 'INSERT INTO NmsCountry (sIsoA2, sIsoA3, iIsoNum, sLabelISO, sLabelFR, sLabelEN, sLabelDE, sIana, sItu, sLabelJA, sPhoneFormat, sPhoneExit)

VALUES (:#(1)#, :#(2)#, :#(3)#, :#(4)#, :#(5)#, :#(6)#, :#(7)#, :#(8)#, :#(9)#, :#(10)#, :#(11)#, :#(12)#)' could not be executed.

INSERT INTO NmsCountry (sIsoA2, sIsoA3, iIsoNum, sLabelISO, sLabelFR, sLabelEN, sLabelDE, sIana, sItu, sLabelJA, sPhoneFormat, sPhoneExit) Param(0)=AL Param(1)=ALB Param(2)=8 Param(3)=ALBANIA
Param(4)=Albanie Param(5)=Albania Param(6)=Albanien Param(7)=al Param(8)=355 Param(9)=アルバニア Param(10)= Param(11)=

Answer 1

This may be because of wrong database encoding.

To check postgreSQL server encoding run psql then run the following command :

postgres=# SHOW SERVER_ENCODING;

If it not UTF8 Then you should change the server encoding using the following steps:

1. Navigate to path/to/postgtres/bin and the open the terminal and run the following command to create backup of your database.

pg_dump your_database > dump.sql​ -p <port_number>

A new file dump.sql is created in the directory.

2. Drop your database.

dropdb your_database​

3. Create new database with the same name of the dropped one and specify the encoding to be UTF8.

createdb -E utf8 your_database​

4. Restore database data from the backup file you previously created.

psql your_database < dump.sql​