How to prevent special characters from being corrupted when using UTF8 encoding in Postgres?

Viewed 44

I'm using PostgreSQL DB with UTF8 encoding and I'm trying to fetch some data using psql.exe.

My table diacritics contains the following data:

id name
1 Kočička
2 Mňau

As you can see, there are a few (Czech) characters with diacritics. They are displayed correctly when viewed in pgAdmin, however, when I try to fetch the same data using psql.exe, the special characters get corrupted:

 id |   name
----+-----------
  1 | Ko─ìi─ìka
  2 | Mňau
(2 rows)

I manually set client encoding to UTF8 as mentioned in this SO answer as I was getting the same error message as the OP there.

My resulting command looks like this (I'm running it in PowerShell):

./psql.exe -h localhost -p 5433 -U postgres -d experimental -c "SET client_encoding TO 'UTF8'; SELECT * FROM diacritics"

I would expect to get the data with the correct diacritics as the encoding on both sides matches. I tried outputting the fetched data into a file to rule out the possibility that it's a PowerShell related issue but had no success.

Also, I can fetch the data correctly in C# using Npgsql package so the problem only seems to be related to using psql.exe.

I'm using PostgreSQL 10.20 (10.0.20.22038). I have also tried it on version 14.2 (14.0.2.22041) and the results are the same.

0 Answers
Related