Using tds_fdw with postgresql in windows
April 6, 2017
Install Postgresql using the bigsql distro
Follow the instructions at the install page.
Install tds_fdw1-pg96
C:\bigsql>pgc install tds_fdw1-pg96
Setup a database and user in SQL Server and a test table
CREATE TABLE test1 (id integer, name varchar(255)); INSERT INTO test1(id, name) VALUES (1, 'hello, world'), (2, 'I am a banana');
Set up the extension in postgres
CREATE EXTENSION tds_fdw; CREATE SERVER tds_test_server FOREIGN DATA WRAPPER tds_fdw OPTIONS ( servername 'localhost', database 'test', port '1433', msg_handler 'notice', character_set 'UTF-8', tds_version '7.1' ); CREATE USER MAPPING FOR testdev SERVER tds_test_server OPTIONS (username 'username', password 'password');
Note that if you do not specify character_set, you will get an unable to connect error and you will bang your head against the wall for hours
Create the foreign table
CREATE FOREIGN TABLE test1 (id integer, name varchar(255)) SERVER tds_test_server OPTIONS (table 'dbo.test1');
Query your new table
SELECT * FROM test1;
Enjoy!