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!