Character with encoding UTF8 has no equivalent in WIN1252

user_75214 0
Viewed 91903

I am getting the following exception:

Caused by: org.postgresql.util.PSQLException: ERROR: character 0xefbfbd of encoding "UTF8" has no equivalent in "WIN1252"

Is there a way to eradicate such characters, either via SQL or programmatically?
(SQL solution should be preferred).

I was thinking of connecting to the DB using WIN1252, but it will give the same problem.

character utf-8 encoding postgresql
9 Answers

I had a very similar issue. I had a linked server from SQL Server to a PostgreSQL database. Some data I had in the table I was selecting from using an openquery statement had some character that didn't have an equivalent in Win1252. The problem was that the System DSN entry (to be found under the ODBC Data Source Administrator) I had used for the connection was configured to use PostgreSQL ANSI(x64) rather than PostgreSQL Unicode(x64). Creating a new data source with the Unicode support and creating a new modified linked server and refernecing the new linked server in your openquery resolved the issue for me. Happy days.

user_65497960

Here's what worked for me : 1 enable ad-hoc queries in sp_configure. 2 add ODBC DSN for your linked PostgreSQL server. 3 make sure you have both ANSI and Unicode (x64) drivers (try with both). 4 run query like this below - change UID, server ip, db name and password. 5 just keep the query in last line in postgreSQL format.

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

SELECT * FROM OPENROWSET('MSDASQL', 
'Driver=PostgreSQL Unicode(x64); 
uid=loginid;
Server=1.2.3.41;
port=5432;
database=dbname;
pwd=password',

'select * FROM table_name limit 10;')
user_37356080

I have face this issue when my Windows 10 using Mandarin China as default language. This problem has occurred because I did try to import a database with UTF-8. Checking via psql and do "\l", it shows collate and cytpe is Mandarin China.

The solution, reset OS language back to US and re-install PostgreSQL. As the collate back to UTF-8, you can reset back your OS language again.

I write the full context and solution here https://www.yodiw.com/fix-utf8-encoding-win1252-cputf8-postgresql-windows-10/

user_10369410
Related