Categories
Mandarin

Fix “UTF8” encoding “WIN1252” CPutf8 PostgreSQL Windows 10

Today I found very weird errors when importing database from Ubuntu to Windows 10 (Chinese language / locale). It’s showing multiple errors start from

locale "English_United States.utf8": codeset is "CPutf8"
Character with encoding UTF8 has no equivalent in WIN1252
ERROR: character with byte sequence 0xe9 0x94 0x99 in encoding "UTF8" has no equivalent in encoding "WIN1252"
character with byte sequence 0xe9 0x94 0x99 in encoding "UTF8" has no equivalent in encoding "WIN1252"

This errors occur because the collate and ctype on current OS have different type than from another database (which is UTF8).

Several solution I did, but not working at all, start from :

open the cmd
SET PGCLIENTENCODING=utf-8
chcp 65001
psql -h your.ip.addr.ess -U postgres

SET client_encoding = 'UTF8';
update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8' 
where datname='mydb';

postgres=# update pg_database set datallowconn = TRUE where datname = 'template0';
UPDATE 1
postgres=# \c template0
You are now connected to database "template0".
template0=# update pg_database set datistemplate = FALSE where datname = 'template1';
UPDATE 1
template0=# drop database template1;
DROP DATABASE
template0=# create database template1 with template = template0 encoding = 'UTF8';
CREATE DATABASE
template0=# update pg_database set datistemplate = TRUE where datname = 'template1';
UPDATE 1
template0=# \c template1
You are now connected to database "template1".
template1=# update pg_database set datallowconn = FALSE where datname = 'template0';
UPDATE 1

None of them works, until I have found the real solution

1.Verify PostgreSQL collation
Access your postgres and type “\l”. If your Collate and Ctype is China Mandarin, then you have same problems as mine. We should change it back to US UTF8

2.Uninstall your Postgresql
Yes, uninstall your postgresql and download the latest version. This is to refresh the locale.

3.Revert your language and settings everything to US language
Yes, this is the major cause for me. Change all the languange and locale. Including the region settings / format. Don’t enable BETA: UTF-8 for world wide language settings.

4.Restart and re-install PostgreSQL
After it’s done. Restart your laptop/computer. Then, install PostgreSQL and choose default locale. Try to import your database again. At this moment, when you switch back to Mandarin China, everything still okay

Hope this help you! Yes, this is super weird issue!

Leave a Reply

Your email address will not be published. Required fields are marked *