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:

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

• Sources: README, foreign server, foreign table, user mapping, foreign schema, variables

tds_fdw is a foreign data wrapper for querying TDS databases such as Sybase and Microsoft SQL Server through a DB-Library implementation such as FreeTDS.

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, local_tuple_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
  OPTIONS (import_default 'true');

Import Options: import_default, import_not_null, and keep_custom_types for preserving Sybase user-defined types when matching PostgreSQL domains already exist.

Planner And Runtime Notes

The upstream README says the current version does not support join pushdown or write operations. It does support WHERE and column pushdown when match_column_names is enabled.

Set diagnostic memory-stat variables with PostgreSQL SET, for example:

SET tds_fdw.show_finished_memory_stats = 1;

Available variables are tds_fdw.show_before_row_memory_stats, tds_fdw.show_after_row_memory_stats, and tds_fdw.show_finished_memory_stats.

Pigsty package metadata is version 2.0.5 from PGDG for PostgreSQL 14-18. Upstream docs say the FDW should support PostgreSQL 9.2+ and the current build matrix includes PostgreSQL 13-18, but this stub follows the packaged PostgreSQL versions from db/extension.csv.