How to use file_fdw and tds_fdw in PostgreSQL

There are scenarios where you need to get external data into your PostgreSQL DB as if they were a real table in your database. In this blog, we will discuss file_fdw (for reading local server files) and tds_fdw (for querying Microsoft SQL Server and Sybase databases).If you’ve read our previous guide on postgres_fdw, you already know how powerful Foreign Data Wrappers (FDWs) can be for seamlessly bridging external data directly into your PostgreSQL environment.

1. Using file_fdw: Querying Flat Files with SQL

The file_fdw extension is a built-in PostgreSQL module that allows you to access data files on the server's file system or execute a program and read its output directly as PostgreSQL tables. It is exceptionally useful for querying server logs, CSV dumps, or configuration files without having to run an INSERT or COPY command.

Step 1: Enable the Extension

Since file_fdw is included in the standard PostgreSQL distribution, you just need to enable it in your database:

CREATE EXTENSION file_fdw;

Step 2: Create the Foreign Server

Next, define the server object. This tells PostgreSQL which wrapper to use.

CREATE SERVER local_files FOREIGN DATA WRAPPER file_fdw;

Step 3: Create the Foreign Table

Now, you map the structure of your flat file to a PostgreSQL table. Let’s assume you have a CSV file located at /var/lib/postgresql/data/employee_list.csv containing headers.

CREATE FOREIGN TABLE employees_csv (
    emp_id integer,
    first_name text,
    last_name text,
    department text
) SERVER local_files
OPTIONS ( 
    filename '/path/to/the/file/employee_list.csv', 
    format 'csv', 
    header 'true' 
);

Querying the Data

You can now run standard SELECT queries against employees_csv. For example:

SELECT * FROM employees_csv WHERE department = 'IT';

Note: file_fdw is strictly read-only. You cannot INSERT, UPDATE, or DELETE data in the foreign table. This feature would be helpful for reporting purposes.

2. Using tds_fdw: Bridging PostgreSQL and MS SQL Server

Connecting to entirely different database engines requires a bit more heavy lifting. The tds_fdw extension uses the Tabular Data Stream (TDS) protocol to connect PostgreSQL to Microsoft SQL Server and Sybase databases.

If you are orchestrating a major migration from a demo MS SQL system to an open-source PostgreSQL stack for platforms like Odoo, tds_fdw allows you to query the demo tables directly from your new database, making data validation and incremental migration significantly easier.

Step 1: Prerequisites & Installation

Unlike file_fdw, tds_fdw is a third-party extension. On Debian/Ubuntu systems, you can usually install it directly via the apt package manager.

You will need to install the package that matches your specific PostgreSQL major version. For example, if you are running PostgreSQL 18, the command is:

sudo apt install postgresql-18-tds-fdw

(Note: Replace 18 with your actual PostgreSQL version, such as 15 or 16. Installing this package automatically pulls in freetds-dev and other required dependencies.)

Step 2: Enable the Extension

Once installed on the OS level, enable it in your database, after connecting to your database:

CREATE EXTENSION tds_fdw;

Step 3: Create the Foreign Server

Define the connection details for your MS SQL Server.

CREATE SERVER mssql_server FOREIGN DATA WRAPPER tds_fdw
OPTIONS (
    servername '192.168.1.100', 
    port '1433', 
    database 'DemoERP_DB', 
    tds_version '7.3'
);

Note: Change the servername into your MSSQL Server IP, your port, database name and tds_version.

Step 4: Create User Mapping

Map your PostgreSQL user to the MS SQL Server credentials (use your credentials here).

CREATE USER MAPPING FOR postgres SERVER mssql_server
OPTIONS (
    username 'sa', 
    password 'YourSecurePassword!'
);

Step 5: Map the Data (Two Approaches)

Once your connection is established, you need to tell PostgreSQL how to view the MS SQL data. You can either map individual tables or pull in an entire schema at once.

Approach A: The Bulk Import (Recommended for Migrations) If you are migrating a whole database, defining tables one-by-one is tedious. Instead, you can create a dedicated local schema in PostgreSQL and import the entire MS SQL schema into it. This automatically creates foreign tables for everything inside that schema.

-- Create a dedicated schema to isolate the demo data
CREATE SCHEMA IF NOT EXISTS demodb_mirror;
-- Import the entire "dbo" schema from MS SQL into our new local schema
IMPORT FOREIGN SCHEMA "dbo" 
FROM SERVER mssql_server 
INTO demodb_mirror;
Now, you can immediately query SELECT * FROM demodb_mirror.customers; without writing a single CREATE FOREIGN TABLE statement!

Approach B: Manual Table Mapping (Best for Granular Control) If you only need access to a single specific table, you can define it manually. You must match the schema exactly as it exists in the MS SQL database.

CREATE FOREIGN TABLE demo_customers_single (
    customer_id integer,
    company_name varchar(255),
    contact_email varchar(255)
) SERVER mssql_server
OPTIONS (
    schema_name 'dbo',
    table_name 'Customers',
    row_estimate_method 'showplan_all'
);

Querying the Data

Just like that, you can join demo_customers sitting in MS SQL Server with your native PostgreSQL tables. tds_fdw supports pushdown for WHERE clauses, meaning the filtering happens on the MS SQL side, saving network bandwidth.

Foreign Data Wrappers are one of PostgreSQL's most defining features. file_fdw provides a frictionless way to expose server-side flat files to your SQL engine, while tds_fdw breaks down the walls between Microsoft and open-source ecosystems.