PostgreSQL: character with byte sequence 0xc2 0x81 in encoding "UTF8" has no equivalent in encoding "WIN1252"

Question

Getting the below exception while executing SELECT query for a particular row on that table

ERROR:  character with byte sequence 0xc2 0x81 in encoding "UTF8" has no equivalent in encoding "WIN1252"

One of the column in that row contains Japanese character which has been encoded with UTF-8 and inserted into it.

Is there any fix for this issue?

Solution 1

You should know what encoding is used in your database.

SHOW server_encoding;

When you connect to your database you can specify what encoding should your client use:

SET client_encoding TO 'UTF8';

If server and client encoding differ, the database driver tries to translate between those two encoding. When it can not find an equivalent character, the error is thrown.

So server encoding and client encoding should be the same to avoid problems like yours.

To fix your problem:

• connect to your database
• set client_encoding to UTF8
• update the row with Japanese characters

To avoid this problem in the future remember to set client_encoding to proper value when you connect to the database.

Check the documentation on Supported Character Sets.

Solution 2

see here

I've encountered this error "ERROR: character with byte sequence <...> in encoding "UTF8" has no equivalent in encoding "WIN1252";" while using MySQL Workbench to migrate data from PostgreSQL to MySQL. I was not sure why this was happening because the target MySQL database had UTF8 encoding and I thought that everything can be mapped to it.

The real root cause of this problem turned out to be the PostgreSQL driver I used to connect to the source DB and specified in the Workbench which was PostgreSQL ODBC Driver (ANSI) and after I changed it to PostgreSQL ODBC Driver (UNICODE) everything worked fine.

Solution 3

For me I was importing data from a csv file, I needed to connect to the database then change the client encoding from WIN1252 to UTF8, then import the data from the csv file:

• psql -h localhost -p 5432 -d timetrex -U postgres; // connect to the database
• set client_encoding to UTF8;
• show client_encoding; // this command will show the current encoding
• While you still inside the database you need to import the file this way: COPY users FROM 'D:/Downloads/users_dump.csv' (format CSV , header true, DELIMITER ',' ,ENCODING 'UTF8') ; // copy the data from the csv file

Comments

[Nicolas Le Thierry d'Ennequin]

Same problem, but it is related to psql (the command line) and console encoding in Windows 7. Unfortunately, @Adam's solution is no help here. I hoped this would help: stackoverflow.com/questions/20952893/… but it doesn't work either. I'm currently stuck.

[Evan Carroll]

Possible duplicate of PostgreSQL: encoding problems on Windows when using psql command line utility

[Raamesh Keerthi]

both are in UTF8 JapanDB=# SHOW server_encoding; server_encoding ----------------- UTF8 (1 row) JapanDB=# show client_encoding; client_encoding ----------------- UTF8 (1 row)

[Matthew Moisen]

Is it possible to default the client_encoding to UTF8 in psql so we don't have to execute it every time?

[Adam]

@MatthewMoisen Yes. Use PGCLIENTENCODING environment variable OR client_encoding configuration variable. See Automatic Character Set Conversion Between Server and Client.

[Ljubisa Livac]

God bless you man!