tds_fdw

Overview

Version

Install

You can install tds_fdw directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install tds_fdw;          # Install for current active PG version

pig ext install -y tds_fdw -v 18  # PG 18
pig ext install -y tds_fdw -v 17  # PG 17
pig ext install -y tds_fdw -v 16  # PG 16
pig ext install -y tds_fdw -v 15  # PG 15
pig ext install -y tds_fdw -v 14  # PG 14

dnf install -y tds_fdw_18       # PG 18
dnf install -y tds_fdw_17       # PG 17
dnf install -y tds_fdw_16       # PG 16
dnf install -y tds_fdw_15       # PG 15
dnf install -y tds_fdw_14       # PG 14

apt install -y postgresql-18-tds-fdw   # PG 18
apt install -y postgresql-17-tds-fdw   # PG 17
apt install -y postgresql-16-tds-fdw   # PG 16
apt install -y postgresql-15-tds-fdw   # PG 15
apt install -y postgresql-14-tds-fdw   # PG 14

Create Extension:

CREATE EXTENSION tds_fdw;

Usage

tds_fdw: Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)

Create Server

CREATE EXTENSION tds_fdw;

CREATE SERVER mssql_svr
  FOREIGN DATA WRAPPER tds_fdw
  OPTIONS (servername '127.0.0.1', port '1433',
           database 'tds_fdw_test', tds_version '7.1');

Server Options: servername (server address or DSN, supports comma-separated failover list), port, database, dbuse (0 for direct connection, non-0 for dbuse()), tds_version (protocol version), language, character_set, msg_handler (notice or blackhole), sqlserver_ansi_mode, fdw_startup_cost, fdw_tuple_cost.

Create User Mapping

CREATE USER MAPPING FOR postgres
  SERVER mssql_svr
  OPTIONS (username 'sa', password 'secret');

For Azure SQL databases, use the format username@servername for the username option.

Create Foreign Table

Map a remote table directly:

CREATE FOREIGN TABLE mssql_table (
  id integer,
  name varchar(255),
  value numeric(10,2)
)
SERVER mssql_svr
OPTIONS (schema_name 'dbo', table_name 'mytable');

Or use a custom SQL query:

CREATE FOREIGN TABLE mssql_query (
  id integer,
  name varchar(255),
  total numeric(10,2)
)
SERVER mssql_svr
OPTIONS (query 'SELECT id, name, SUM(amount) AS total FROM orders GROUP BY id, name');

Table Options: table_name or query (one required, mutually exclusive), schema_name, match_column_names (map by name vs position), use_remote_estimate, row_estimate_method (execute or showplan_all).

Column Options: column_name (remote column name if different from local).

Query and Debug

SELECT * FROM mssql_table WHERE id > 100;

-- View the remote query sent to SQL Server
EXPLAIN (VERBOSE) SELECT * FROM mssql_table WHERE id > 100;

Import Foreign Schema

IMPORT FOREIGN SCHEMA dbo
  FROM SERVER mssql_svr
  INTO public;