postgresql and MSSQL - foreign data wrapper tds_fdw

493 views Asked by At

I am trying to see data on postgresql from MSSQL:

Table on MSSQL (data already populated):

CREATE TABLE aaa.dbo.pct1 (
    a nvarchar(50) COLLATE Latin1_General_CI_AI DEFAULT '' NULL
);

Preparing env on postgres:

psql -h server_name -U pc -d pc_db
Timing is on.
psql (14.7 (Debian 14.7-1.pgdg110+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

pc_db=> select extname,extversion from pg_extension where extname='tds_fdw';
 extname | extversion
---------+------------
 tds_fdw | 2.0.2
(1 row)

Time: 0.690 ms
pc_db=>
pc_db=> SHOW CLIENT_ENCODING;
 client_encoding
-----------------
 UTF8
(1 row)

Time: 0.538 ms
pc_db=>

pc_db=> CREATE SERVER foreign_fe_server
pc_db-> FOREIGN DATA WRAPPER tds_fdw
pc_db->     OPTIONS (servername '<sqlserver>', port '1433', database 'aaa' );
CREATE SERVER
Time: 5.733 ms
pc_db=>
pc_db=> CREATE USER MAPPING FOR pc_dba
pc_db-> SERVER foreign_fe_server
pc_db->     OPTIONS (username 'pc', password '<password>');
CREATE USER MAPPING
Time: 1.918 ms
pc_db=>
pc_db=> CREATE FOREIGN TABLE pct1 ( a text)
pc_db-> SERVER foreign_fe_server OPTIONS (table 'pct1');
CREATE FOREIGN TABLE
Time: 4.945 ms
pc_db=>
pc_db=> select * from pct1 limit 2;
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
ERROR:  DB-Library error: DB #: 2404, DB Msg: Buffer overflow converting characters from client into server's character set, OS #: 0, OS Msg: Success, Level: 4
Time: 94.760 ms
pc_db=>

Can you please help me to properly format Latin1_General_CI_AI to UTF-8 ?

I've tried to create collation with no success:

CREATE COLLATION general_ci_ai (
   PROVIDER = icu,
   DETERMINISTIC = FALSE,
   LOCALE = '@ColStrength=primary'
);
CREATE FOREIGN TABLE pct1 ( a text COLLATE general_ci_ai )
SERVER foreign_fe_rr_server OPTIONS (table 'pct1');
0

There are 0 answers