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');