How to SQLServer open source in PostgreSQL

Galaxy Glossary

How can PostgreSQL query SQL Server using open-source tools?

SQLServer open source lets PostgreSQL query Microsoft SQL Server via the free tds_fdw extension.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why use an open-source bridge to SQL Server?

Running reports across PostgreSQL and Microsoft SQL Server is simpler when you can query both from one place. The tds_fdw foreign data wrapper (FDW) is open-source and makes SQL Server tables appear local inside PostgreSQL.

What are the prerequisites?

Install PostgreSQL 13+ and compile tds_fdw against FreeTDS. Obtain a SQL Server login with read rights to the target database.

How do I enable the extension?

Load the shared library, then create the extension: CREATE EXTENSION tds_fdw;

How do I define the remote server?

Use CREATE SERVER with SQL Server host, port, and TDS version. Specify OPTIONS like database and tds_version.

Example

CREATE SERVER mssql_srv FOREIGN DATA WRAPPER tds_fdw OPTIONS (host '10.0.0.25', port '1433', database 'ecommerce', tds_version '7.4');

How do I map PostgreSQL users?

Create a user mapping to pass SQL Server credentials safely.

Example

CREATE USER MAPPING FOR analytics_user SERVER mssql_srv OPTIONS (username 'report', password 'S3cRe7');

How do I import SQL Server tables?

Either define foreign tables manually or use IMPORT FOREIGN SCHEMA for bulk import.

Manual definition

```sqlCREATE FOREIGN TABLE sqlserver_products ( id int, name text, price numeric, stock int) SERVER mssql_srv OPTIONS (schema_name 'dbo', table_name 'Products');```

How do I join PostgreSQL and SQL Server data?

Once foreign tables exist, treat them like local ones. PostgreSQL handles the push-down.

Query example

```sqlSELECT p.name, p.price, oi.quantityFROM sqlserver_products pJOIN OrderItems oi ON oi.product_id = p.idORDER BY oi.quantity DESC;```

Best practices for production?

Limit column lists, create statistics with ANALYZE, and isolate FDW usage in a dedicated schema. Monitor network latency because cross-DB joins may be chatty.

What pitfalls should I avoid?

Avoid huge cross-joins without filters; they pull entire SQL Server tables. Always match data types between PostgreSQL and SQL Server to prevent silent casts.

Why How to SQLServer open source in PostgreSQL is important

How to SQLServer open source in PostgreSQL Example Usage


-- Find top-selling products stored in SQL Server over the last month
SELECT p.name,
       SUM(oi.quantity) AS units_sold,
       SUM(oi.quantity * p.price) AS revenue
FROM sqlserver_products p
JOIN OrderItems oi ON oi.product_id = p.id
JOIN Orders o ON o.id = oi.order_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY p.name
ORDER BY revenue DESC
LIMIT 10;

How to SQLServer open source in PostgreSQL Syntax


-- Enable tds_fdw
CREATE EXTENSION IF NOT EXISTS tds_fdw;

-- Register the remote SQL Server
CREATE SERVER mssql_srv
    FOREIGN DATA WRAPPER tds_fdw
    OPTIONS (
        host '10.0.0.25',          -- SQL Server address
        port '1433',               -- default port
        database 'ecommerce',      -- target DB
        tds_version '7.4'          -- protocol version
    );

-- Map a PostgreSQL role to SQL Server credentials
CREATE USER MAPPING FOR analytics_user
    SERVER mssql_srv
    OPTIONS (
        username 'report',
        password 'S3cRe7'
    );

-- Import the Products table
CREATE FOREIGN TABLE sqlserver_products (
    id int,
    name text,
    price numeric,
    stock int
) SERVER mssql_srv OPTIONS (
    schema_name 'dbo',
    table_name 'Products'
);

-- Join local Orders and remote Products
SELECT o.id, p.name, p.price, oi.quantity
FROM Orders o
JOIN OrderItems oi ON oi.order_id = o.id
JOIN sqlserver_products p ON p.id = oi.product_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days';

Common Mistakes

Forgetting to match data types when defining foreign tables results in runtime cast errors. Fix by aligning column types exactly with SQL Server definitions.
Running unfiltered SELECT * queries on large SQL Server tables pulls all rows across the network. Always add WHERE clauses or LIMIT and use column lists.