SQL Server to PostgreSQL Migration Guide

Planning a SQL Server to PostgreSQL migration

A SQL Server to PostgreSQL migration is heterogeneous: the engines share standard SQL but differ in types, procedural language, identifier rules and system functions. Before choosing a tool, list the schemas and table sizes, count the stored procedures, functions, triggers and views, find SQL Server features with no direct PostgreSQL equivalent (SQL Agent jobs, linked servers, CLR objects, full-text catalogues), and record every application that connects. The SQL Server vs PostgreSQL comparison summarises the engine differences, and the database migration guide covers the general process.

There are three realistic routes:

• Convert to native PostgreSQL with pgloader (schema and data) or DMS Schema Conversion plus AWS DMS (schema, code and data, with optional CDC). Applications are then changed to PostgreSQL drivers and SQL.
• Babelfish for Aurora PostgreSQL, which accepts SQL Server clients over TDS on port 1433 and runs T-SQL with documented differences, so applications change less. It is specific to Amazon Aurora.
• Manual: script the schema yourself, export data with bcp and load it with PostgreSQL's COPY. Practical only for small schemas.

SQL Server to PostgreSQL data type mapping

The middle column shows what pgloader does by default; the right column is the usual hand-written choice. pgloader turns every character type into text and every datetime into timestamptz; PostgreSQL's text has no length limit, so if you rely on lengths for validation, keep varchar(n) instead.

T-SQL to PostgreSQL: what to rewrite

Procedural code is where most effort goes. The table lists the constructs that appear in almost every SQL Server code base and their PostgreSQL forms. PostgreSQL procedures (CREATE PROCEDURE) do not return result sets the way T-SQL procedures do, so procedures that end in a SELECT usually become functions that RETURNS TABLE; DMS Schema Conversion has a setting for exactly this choice.

Tools: pgloader, AWS DMS and Babelfish compared

pgloader's stable release is 3.6.9; its documentation now also describes a Java-based v4 published as a development build. For the AWS route step by step, including CDC prerequisites on SQL Server and pricing, see the AWS DMS guide. For Babelfish, AWS recommends running Babelfish Compass on your generated DDL to measure how much T-SQL is supported before committing.

Worked example: one table and one procedure

The source is a SQL Server table with an identity key and a procedure that returns a customer's most recent orders. The PostgreSQL version uses an identity column, boolean, timestamp and a function returning a table. The load is done with a pgloader command file that copies the dbo schema into public; pgloader documents its default options for database sources as creating tables, indexes and foreign keys and resetting sequences.

CREATE TABLE dbo.orders (
    order_id     INT IDENTITY(1,1) PRIMARY KEY,
    customer_id  INT           NOT NULL,
    status       NVARCHAR(20)  NULL,
    is_paid      BIT           NOT NULL DEFAULT 0,
    total_amount DECIMAL(12,2) NOT NULL,
    created_at   DATETIME2     NOT NULL DEFAULT GETDATE()
);
GO
CREATE PROCEDURE dbo.get_recent_orders @customer_id INT, @top_n INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT TOP (@top_n) order_id, ISNULL(status, N'new') AS status, created_at
    FROM dbo.orders
    WHERE customer_id = @customer_id
    ORDER BY created_at DESC;
END;

CREATE TABLE orders (
    order_id     integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_id  integer       NOT NULL,
    status       varchar(20),
    is_paid      boolean       NOT NULL DEFAULT false,
    total_amount numeric(12,2) NOT NULL,
    created_at   timestamp     NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION get_recent_orders(p_customer_id integer, p_top_n integer)
RETURNS TABLE (order_id integer, status text, created_at timestamp)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT o.order_id, COALESCE(o.status, 'new')::text, o.created_at
    FROM orders AS o
    WHERE o.customer_id = p_customer_id
    ORDER BY o.created_at DESC
    LIMIT p_top_n;
END;
$$;

-- T-SQL: EXEC dbo.get_recent_orders 42, 10;   PostgreSQL:
SELECT * FROM get_recent_orders(42, 10);

LOAD DATABASE
     FROM mssql://<user>:<your-password>@<sqlserver-host>/mydb
     INTO postgresql://<pg-user>:<your-password>@<pg-host>/mydb

ALTER SCHEMA 'dbo' RENAME TO 'public';

SELECT COUNT(*) AS row_count, SUM(total_amount) AS total, MAX(order_id) AS max_id
FROM orders;

-- if you created the table yourself and loaded explicit ids,
-- move the identity past the highest copied value
SELECT setval(pg_get_serial_sequence('orders', 'order_id'),
              (SELECT MAX(order_id) FROM orders));

Testing, cutover and rollback

• Compare data table by table with counts, sums and key ranges, then compare sample rows column by column. Watch for strings that change length because of collation or trailing spaces, and timestamps shifted by a time zone if you chose timestamptz.
• Check case sensitivity. If your SQL Server collation is case-insensitive (its name contains _CI_), queries such as WHERE email = @email matched regardless of case. On PostgreSQL, the documented options are comparing lower() on both sides, the citext extension (a case-insensitive text type that calls lower internally), or a nondeterministic collation.
• Run the application test suite against PostgreSQL, including reports and scheduled jobs that used SQL Agent.
• Cut over with writes stopped, sequences reset, and connection strings switched; keep the SQL Server database untouched until the agreed rollback window closes.

For backups of the new database, see the related pg_dump guide; to manage it day to day, see pgAdmin or DBeaver. If you are learning PostgreSQL SQL after years of T-SQL, the SQL beginner course covers the shared basics.

Frequently asked questions

Sources

• pgloader documentation: MS SQL to Postgres
• pgloader releases (GitHub)
• pgloader documentation: Installing pgloader
• AWS DMS User Guide: SQL Server to PostgreSQL conversion settings
• AWS DMS User Guide: DMS Schema Conversion
• AWS DMS User Guide: Sources for data migration
• Aurora User Guide: Babelfish for Aurora PostgreSQL
• Aurora User Guide: Migrating a SQL Server database to Babelfish
• Aurora User Guide: Differences between Babelfish and SQL Server
• Babelfish project site
• PostgreSQL: CREATE TABLE
• PostgreSQL: citext module
• pgloader documentation: MySQL to Postgres (default options)
• PostgreSQL: Character types
• PostgreSQL: UUID type
• PostgreSQL: Binary data types
• PostgreSQL: Lexical structure (identifiers)
• PostgreSQL: SELECT (LIMIT)
• PostgreSQL: Conditional expressions (COALESCE)
• PostgreSQL: Date/time functions
• PostgreSQL: String functions and operators
• PostgreSQL: Returning data from modified rows
• PostgreSQL: MERGE
• PostgreSQL: PL/pgSQL control structures
• PostgreSQL: CREATE PROCEDURE
• PostgreSQL: System information functions
• PostgreSQL: Sequence functions
• Microsoft Learn: TOP (Transact-SQL)
• Microsoft Learn: ISNULL (Transact-SQL)
• Microsoft Learn: GETDATE (Transact-SQL)
• Microsoft Learn: TRY...CATCH (Transact-SQL)
• Microsoft Learn: IDENTITY property
• Microsoft Learn: uniqueidentifier

Checked 8 October 2026.

How we research guides: our editorial method. We link only to official downloads and never host installers.