How to SQLServer cloud native in PostgreSQL

Galaxy Glossary

How do I connect PostgreSQL to a cloud SQL Server instance natively?

SQLServer_FDW lets PostgreSQL query SQL Server tables natively in a cloud-first setup.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why use SQLServer_FDW for cloud-native access?

Running micro-services in Kubernetes often means mixing databases. SQLServer_FDW keeps data where it lives while enabling PostgreSQL to JOIN, SELECT, or INSERT into SQL Server—no ETL.

How do I install the extension?

Connect as superuser and run CREATE EXTENSION IF NOT EXISTS sqlserver_fdw;. The extension ships in popular images like postgres:alpine; otherwise, install the postgresql-XX-sqlserver-fdw package.

What is the basic syntax?

Create a foreign server, user mapping, and foreign table.Use options to define SQL Server host, port, and database. COPY bulk loads are also supported.

How do I query SQL Server tables?

After defining the foreign table, reference it like any PostgreSQL table. Combine it with local tables for analytics, or wrap it in a view for developers.

Can I write back to SQL Server?

Yes—INSERT, UPDATE, and DELETE work if readonly is off in IMPORT FOREIGN SCHEMA.Use transactions for safety.

Best practices

Place the FDW in its own schema to avoid name clashes.
Use IMPORT FOREIGN SCHEMA to refresh metadata automatically.
Add COLUMN OPTIONS (column_name 'true') to push filters down for performance.

Common mistakes

Missing COLLATE—SQL Server uses case-insensitive collations. Explicitly cast text with COLLATE "C" in JOINs.
Forgetting firewall rules—Cloud MSSQL needs an inbound rule for the Postgres pod IP.

Example: join SQL Server Orders with local Products

The query below shows real-time SQL Server order totals beside PostgreSQL product cost.

SELECT p.id, p.name, oi.quantity, oi.quantity * p.price AS line_total FROM orderitems_mssql oi JOIN products p ON p.id = oi.product_id;.

Why How to SQLServer cloud native in PostgreSQL is important

How to SQLServer cloud native in PostgreSQL Example Usage


-- Join cloud SQL Server Orders with local Customers
SELECT c.name, o.total_amount
FROM Customers c
JOIN ext_mssql.Orders o ON o.customer_id = c.id
WHERE o.total_amount > 100;

How to SQLServer cloud native in PostgreSQL Syntax


-- 1. Create foreign server
CREATE SERVER mssql_cloud
  FOREIGN DATA WRAPPER sqlserver_fdw
  OPTIONS (servername 'mssql.company.cloud', port '1433', database 'ecommerce');

-- 2. Map Postgres role to SQL Server login
CREATE USER MAPPING FOR app_user
  SERVER mssql_cloud
  OPTIONS (username 'readonly', password 'Str0ngP@ss');

-- 3. Import the Orders and OrderItems tables
IMPORT FOREIGN SCHEMA dbo
  LIMIT TO (Orders, OrderItems)
  FROM SERVER mssql_cloud INTO ext_mssql;

-- 4. Query as if local
SELECT id, customer_id, total_amount
FROM ext_mssql.Orders
WHERE order_date > CURRENT_DATE - INTERVAL '7 days';

Common Mistakes

Creating the foreign server without specifying the ‘database’ option. PostgreSQL then defaults to <null> and every query fails. Always supply <code>database</code>.
Using SUPERUSER for user mapping. This leaks credentials and violates least-privilege. Map a limited Postgres role to a read-only or CRUD SQL Server login instead.