SQL Server to PostgreSQL Migration: Tools, T-SQL Differences and Data Types

The short answer

There are two ways to move from Microsoft SQL Server to PostgreSQL. The conversion route moves schema and data with pgloader or AWS DMS and rewrites T-SQL procedures, functions and application queries for PostgreSQL, with AWS SCT converting much of the code. The compatibility route runs Babelfish for PostgreSQL, an open source project that gives PostgreSQL a SQL Server wire-protocol endpoint and a T-SQL dialect, so applications can connect with their existing drivers and change less code. On AWS, Babelfish is available as a feature of Aurora PostgreSQL.

Whichever route you take, four differences account for most of the work: T-SQL syntax, identity columns, collation and case sensitivity, and datetime types. Each is covered below.

SQL Server to PostgreSQL migration tools

For a free SQL Server to PostgreSQL migration tool, pgloader covers schema and data and Babelfish Compass covers assessment. The open source tools do not remove the T-SQL rewrite; they decide how much of it you face.

Migrating with pgloader

pgloader's documented minimum is one command with two connection strings:

pgloader mssql://user@mshost/dbname pgsql://pguser@pghost/dbname

For real migrations, use a load file to filter tables, rename schemas and override casts. Two default casts deserve a look before the first run. pgloader maps datetime and datetime2 to timestamptz, but SQL Server's types carry no time zone, so values are interpreted in the session's zone on the way in. If the source stores local times without a zone, cast them to timestamp instead. pgloader also renames nothing by default, so dbo arrives as a schema called dbo unless you rename it.

load database
  from mssql://user@mshost/sales
  into postgresql://pguser@pghost/sales
  cast type datetime to timestamp, type datetime2 to timestamp
  including only table names like 'Order%' in schema 'dbo'
  alter schema 'dbo' rename to 'public';

pgloader's documentation covers tables, indexes, keys and views. Plan the T-SQL code conversion as a separate workstream, using AWS SCT or by hand.

Keeping T-SQL with Babelfish

Babelfish adds a second endpoint to PostgreSQL that speaks SQL Server's TDS protocol and understands T-SQL, so an application can keep its SQL Server driver and much of its SQL. Babelfish for Aurora PostgreSQL listens for T-SQL clients on port 1433 and PostgreSQL clients on port 5432, and supports TDS versions 7.1 to 7.4. Both AWS and the project document differences from SQL Server, and the project keeps a list of unsupported features, including CLR routines and some built-in functions. Run Babelfish Compass against your schema scripts before committing to this route.

Babelfish suits applications you cannot easily change, such as third-party software or code with no active team. The trade-off is that you run Babelfish's PostgreSQL build, not community PostgreSQL, and your code is still T-SQL. It can also be a first step: leave SQL Server now, then move the code to native PostgreSQL over time.

T-SQL differences

The procedure row is the one that reshapes code. SQL Server procedures often return rows to the caller as a side effect. In PostgreSQL that is a function's job, so procedures that return data usually become set-returning functions, and the calling code changes from EXEC to SELECT * FROM.

Identity columns

SQL Server's IDENTITY(1,1) maps to a PostgreSQL identity column. Use GENERATED BY DEFAULT during the migration so the load can insert existing keys, which is what SET IDENTITY_INSERT ON did in SQL Server. With GENERATED ALWAYS, explicit values need OVERRIDING SYSTEM VALUE on every insert.

CREATE TABLE customers (
  customer_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email text NOT NULL
);

-- After loading data, move the sequence past the highest key
SELECT setval(pg_get_serial_sequence('customers', 'customer_id'),
              (SELECT max(customer_id) FROM customers));

SQL Server SEQUENCE objects map directly to PostgreSQL sequences. Neither system promises gap-free values, so code that assumed consecutive keys was already wrong on SQL Server.

Collation and case sensitivity

This is the difference most likely to break an application quietly. SQL Server's default server collation for US English systems is SQL_Latin1_General_CP1_CI_AS, and the CI means case-insensitive: WHERE email = 'Ana@Example.com' matches ana@example.com. PostgreSQL's standard collations are deterministic, so the same query matches only the exact bytes. There are three fixes:

• Nondeterministic collations (PostgreSQL 12 and later). Create an ICU collation with deterministic = false and apply it to the columns that need case-insensitive comparison. The docs note a performance cost and that some pattern-matching operations do not work with them.
• citext, a case-insensitive text type in contrib. The PostgreSQL docs now suggest nondeterministic collations instead, because they handle more Unicode cases correctly.
• lower() with an expression index, where the application can be changed to compare lower-cased values.

CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
ALTER TABLE customers ALTER COLUMN email TYPE text COLLATE case_insensitive;

Identifiers differ too. PostgreSQL folds unquoted names to lower case, so a table created as "Customers" with quotes must always be quoted. Converting all names to lower case during the migration avoids a long tail of "relation does not exist" errors.

Datetime types

If the application reads back the offset it wrote to a datetimeoffset column, store the offset in its own column. Check also for code that relies on datetime rounding, such as range queries that end at 23:59:59.997; on PostgreSQL, use a half-open range that ends before midnight of the next day.

SQL Server to Aurora PostgreSQL on AWS

On AWS, there are two paths. Convert with AWS SCT, then load and replicate with DMS into Aurora PostgreSQL or RDS for PostgreSQL. Or enable Babelfish on an Aurora PostgreSQL cluster and point the application's SQL Server driver at it, after running Compass or SCT's Babelfish assessment report. The first gives you native PostgreSQL code; the second gets you off SQL Server sooner and leaves T-SQL to convert later.

Where OSSeva fits

OSSeva for PostgreSQL supports community PostgreSQL wherever it runs, from the day of cutover, including patched builds for versions past community end of life, priced per cluster. Migration planning is part of the Assure tier. If your plan depends on Babelfish to keep T-SQL running, the Babelfish project or Aurora's managed Babelfish is the better fit, because OSSeva supports community PostgreSQL rather than Babelfish's modified build. For the move off Oracle rather than SQL Server, see the Oracle to PostgreSQL migration guide, and for running PostgreSQL after the move, the PostgreSQL major version upgrade guide.

Frequently asked questions

What is the best SQL Server to PostgreSQL migration tool?

pgloader for schema and data in one command, AWS SCT for converting T-SQL code, AWS DMS for a low-downtime cutover with ongoing replication, and Babelfish if you want to keep T-SQL running on PostgreSQL.

Is there a free SQL Server to PostgreSQL migration tool?

Yes. pgloader, Babelfish for PostgreSQL, Babelfish Compass and ora2pg are all open source.

How do you migrate Microsoft SQL Server to PostgreSQL using pgloader?

Run pgloader mssql://user@host/db pgsql://user@host/db for a default migration, or write a load file to filter tables, rename the dbo schema and override casts such as datetime to timestamp. Convert procedures and functions separately.

What are the main SQL Server to PostgreSQL migration challenges?

Case-insensitive collations that PostgreSQL does not apply by default, procedures that return result sets, datetimeoffset values whose offsets PostgreSQL does not store, and T-SQL syntax throughout application code.

How do you migrate SQL Server to Aurora PostgreSQL?

Either convert with AWS SCT and move data with DMS, or turn on Babelfish for Aurora PostgreSQL and connect the application through its SQL Server-compatible endpoint.