postgresqlforeign-data-wrappertds-fdw

PostgreSql foreign table select fails due to special characters rows


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:


Solution

  • 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)