PostgreSql foreign table select fails due to special characters rows

Question

I just set up a new foreign table and it works as intended if I just select the "ID" (integer) field.

When I add the "Description"(text) field and try to select the table, it fails with this error message:

utf-8 'Codec cannot decode byte 0xfc in position 10: invalid start byte

After checking the remote table, I found that "Description" contains special characters like: "ö, ü, ä"

What can i do to fix this?

---

Table definitions (Only first 2 rows)

Remote table:

CREATE TABLE test (
    [Id] [char](8) NOT NULL,
    [Description] [nvarchar](50) NOT NULL
)

Foreign table:

Create Foreign Table "Test" (
    "Id" Char(8),
    "Description" VarChar(50)
) Server "Remote" Options (
    schema_name 'dbo', table_name 'test'
);

---

Additional information:

• Foreign data wrapper: tds_fdw
• Local server: Postgres 12, encoding: UTF8
• Remote server: Sql Server, encoding: Latin1_General_CI_AS

Answer 1

As Laurenz Albe suggested in the comments, I created a freetds.conf in my PostgreSQL folder with the following content:

[global]
tds version = auto
client charset = UTF-8

Don't forget to set the path to the configuration file in the environment variable FREETDS.

Powershell:

[System.Environment]::SetEnvironmentVariable('FREETDS','C:\Program Files\PostgreSQL\12',[System.EnvironmentVariableTarget]::Machine)