How to Use SQL Server Data in PostgreSQL

Galaxy Glossary

How do I query SQL Server data from PostgreSQL?

Access SQL Server tables from PostgreSQL through the tds_fdw foreign-data wrapper.

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 query SQL Server from PostgreSQL?

Migrating gradually, unifying reporting, or joining SQL Server data with PostgreSQL tables becomes simpler when both sources are available in one place. tds_fdw lets you read SQL Server tables as if they were local.

Which extension enables SQL Server access?

Use the tds_fdw extension. It leverages FreeTDS to speak the Tabular Data Stream protocol that SQL Server uses.

How do I install and load tds_fdw?

Install the OS package (e.g., apt install postgresql-15-tds-fdw freetds-bin).Then load it per database with CREATE EXTENSION tds_fdw;

What connection objects are required?

CREATE SERVER

Create a foreign server that stores host, port, and default database.

CREATE USER MAPPING

Attach SQL Server credentials to a PostgreSQL role so queries can authenticate.

IMPORT FOREIGN SCHEMA / CREATE FOREIGN TABLE

Define which remote tables should appear locally. Use IMPORT for bulk or CREATE FOREIGN TABLE for single tables.

How do I join SQL Server and PostgreSQL tables?

Once foreign tables exist, use standard JOIN syntax.PostgreSQL handles pushing as much work as possible to SQL Server and merges the rest locally.

Best practices for performance?

Select only needed columns, filter early, and add indexes on SQL Server in the same way you would for local queries. Enable USE_REMOTE_ESTIMATE on for accurate planner stats.

What are common mistakes?

Mis-matching data types and forgetting to quote mixed-case SQL Server column names cause most errors.Review type mapping and use double quotes where necessary.

Can I write back to SQL Server?

Yes—tds_fdw supports INSERT, UPDATE, and DELETE if the SQL Server login has rights. FOREIGN TABLE must include a primary key.

Why How to Use SQL Server Data in PostgreSQL is important

How to Use SQL Server Data in PostgreSQL Example Usage


--Join SQL Server 'Orders' with local 'Customers' in PostgreSQL
SELECT  c.id,
        c.name,
        o.total_amount,
        o.order_date
FROM    Customers   c
JOIN    Orders      o   ON o.customer_id = c.id
WHERE   o.order_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY o.order_date DESC;

How to Use SQL Server Data in PostgreSQL Syntax


--Enable extension
CREATE EXTENSION IF NOT EXISTS tds_fdw;

--Register remote SQL Server
CREATE SERVER mssql_srv
  FOREIGN DATA WRAPPER tds_fdw
  OPTIONS (servername '10.0.0.25', port '1433', database 'ecommerce');

--Map credentials
CREATE USER MAPPING FOR analytics
  SERVER mssql_srv
  OPTIONS (username 'report_user', password 'S3cret!');

--Import only product and order tables
IMPORT FOREIGN SCHEMA dbo
  LIMIT TO (Products, Orders, OrderItems)
  FROM SERVER mssql_srv INTO public;

--Optional: manual table definition
CREATE FOREIGN TABLE public.products_mssql (
  id            integer,
  name          text,
  price         numeric(10,2),
  stock         integer
) SERVER mssql_srv
  OPTIONS (schema_name 'dbo', table_name 'Products');

Common Mistakes

Omitting the FreeTDS driver in <code>freetds.conf</code>. Without the correct driver, the connection fails. Install FreeTDS and verify the <code>tds version</code> matches your SQL Server.
Using mismatched datatypes (e.g., SQL Server <code>MONEY</code> to PostgreSQL <code>numeric</code> with fewer decimals). Mismatches cause rounding or overflow. Explicitly cast or widen the target type.