Question 201120 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL Japanese characters encoding issue

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.

enter image description here

enter image description here

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

Tags:

2

Answers


  1. 0

    Try the following:

    1. Ensure that the character encoding of your database is set correctly to support Japanese characters. The most common encoding for Japanese is UTF-8.
    2. If you’re inserting from a programming language, make sure the strings containing Japanese characters are encoded as UTF-8 before inserting them into the database. To test this, try inserting a simple Japanese string directly, using an SQL query.
    3. Collation settings can also affect how characters are sorted in a database. Ensure that the collation settings for the relevant columns or tables are appropriate for Japanese characters.
    Login or Signup to reply.
  2. 0

    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.

    1. Drop your database.
    dropdb your_database​
    1. Create new database with the same name of the dropped one and specify the encoding to be UTF8.
    createdb -E utf8 your_database​
    1. Restore database data from the backup file you previously created.
    psql your_database < dump.sql​
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search