Using TDS foreign data wrapper v2

Suggest edits

This example shows how to use tds_fdw to query a table on a Microsoft SQL Server from a Postgres database.

Prerequisites

Creating a foreign table

Using a table name

To map a remote table to a foreign table on the Postgres server:

CREATE FOREIGN TABLE mssql_table (
    id integer,
    data varchar)
    SERVER mssql_svr
    OPTIONS (schema_name 'dbo', table_name 'mytable', row_estimate_method 'showplan_all');

Using a query

To map a custom SQL query to a foreign table:

CREATE FOREIGN TABLE mssql_table (
    id integer,
    data varchar)
    SERVER mssql_svr
    OPTIONS (query 'SELECT * FROM dbo.mytable', row_estimate_method 'showplan_all');

Mapping a remote column name

If a local column name differs from the remote column name, use the column_name option:

CREATE FOREIGN TABLE mssql_table (
    id integer,
    local_col varchar OPTIONS (column_name 'remote_col'))
    SERVER mssql_svr
    OPTIONS (schema_name 'dbo', table_name 'mytable');

Querying a foreign table

Once a foreign table is set up, you can query it like any local table:

SELECT * FROM mssql_table WHERE id = 42;

tds_fdw supports WHERE clause pushdown and column pushdown, which means filters and column selections are evaluated on the remote server rather than locally.

tds_fdw doesn't currently support JOIN pushdown or write operations (INSERT, UPDATE, DELETE).

Importing a foreign schema

You can import an entire remote schema rather than defining each foreign table manually:

IMPORT FOREIGN SCHEMA dbo
    EXCEPT (mssql_table)
    FROM SERVER mssql_svr
    INTO public
    OPTIONS (import_default 'true');

Foreign schema options

OptionRequiredDefaultDescription
import_defaultNofalseWhether to include column DEFAULT expressions in the foreign table definitions.
import_not_nullNotrueWhether to include column NOT NULL constraints in the foreign table definitions.
keep_custom_typesNofalseSybase only. When false, user-defined types are resolved to their underlying system types. When true, the original user-defined types are preserved. If you enable this, make sure the equivalent domain types exist in Postgres before importing.

Using EXPLAIN

EXPLAIN (VERBOSE) shows the query issued on the remote system, along with cost-related parameters:

EXPLAIN (VERBOSE) SELECT * FROM mssql_table;

Configuring runtime variables

tds_fdw provides the following variables for debugging memory usage. Set them with the SET command:

SET tds_fdw.show_before_row_memory_stats = 1;
SET tds_fdw.show_after_row_memory_stats = 1;
SET tds_fdw.show_finished_memory_stats = 1;
VariableDescription
tds_fdw.show_before_row_memory_statsPrints memory context stats to the PostgreSQL log before each row is fetched.
tds_fdw.show_after_row_memory_statsPrints memory context stats to the PostgreSQL log after each row is fetched.
tds_fdw.show_finished_memory_statsPrints memory context stats to the PostgreSQL log when a query finishes.

Troubleshooting

To get more detail from tds_fdw when diagnosing issues, increase the verbosity of console messages and set the message handler to notice:

SET client_min_messages TO DEBUG3;
ALTER SERVER mssql_svr OPTIONS (SET msg_handler 'notice');

Then run your queries and review the full output. You can report issues on the GitHub's tds_fdw issues page.

Identifying the tds_fdw version

TDS foreign data wrapper allows you to identify the currently installed version of the extension by querying a system catalog. To check the version, connect to your Postgres server and enter:

SELECT extversion FROM pg_extension WHERE extname = 'tds_fdw';
Output
 extversion
------------
 2.0.5
(1 row)
↑ Up

TDS Foreign Data Wrapper