SQL Server to PostgreSQL migration: what actually breaks

Most SQL Server to PostgreSQL migrations do not fail on the data. Moving rows is the boring part — pgloader or a hand-rolled ETL will handle it. They fail on the things nobody inventoried: a collation that silently changed how string comparisons behave, a stored procedure returning a result set the way only T-SQL can, and forty SQL Agent jobs that have no home on the other side.

This is what we check before quoting one, in the order we check it.

Why teams move in the first place

Usually licensing. SQL Server Standard is priced per core and Enterprise considerably higher, and the bill scales with hardware you were going to buy anyway. Beyond cost, the common drivers are containerisation, avoiding a single-vendor dependency, and wanting the same engine in development, staging and production without licence juggling.

The reason that does not hold up on its own: “PostgreSQL is faster.” Sometimes it is, sometimes it is not, and a badly indexed query is slow on both. If performance is the only argument, fix the indexes first — that is a week of work instead of a quarter.

What actually breaks

Collation and case sensitivity

This is the one that bites hardest and gets noticed last. SQL Server databases are very often created with a case-insensitive collation such as SQL_Latin1_General_CP1_CI_AS. PostgreSQL compares strings case-sensitively by default.

So this returns a row on SQL Server and nothing on PostgreSQL:

SELECT * FROM users WHERE email = 'John@Example.com';
-- stored value: 'john@example.com'

Nothing errors. Logins just start failing, lookups return empty, and duplicate rows appear where a unique constraint used to catch them. The options are citext, lower() with a functional index, or a non-deterministic ICU collation — but the decision has to be made deliberately, per column, before the data moves.

Data types

Most map cleanly. The ones worth deciding on rather than defaulting:

• •DATETIME / DATETIME2 → timestamp. DATETIME rounds to roughly 3ms increments; if anything depends on that rounding, behaviour changes.
• •UNIQUEIDENTIFIER → uuid, but check how the application generates them — NEWSEQUENTIALID() has no direct equivalent.
• •MONEY → numeric(19,4). Never map it to a floating-point type.
• •BIT → boolean, and every 0/1 comparison in application code has to follow.
• •NVARCHAR → text or varchar. PostgreSQL is UTF-8 throughout, so the N prefix stops meaning anything.
• •VARBINARY / IMAGE → bytea, or move the blobs out of the database entirely while you are in there.
• •IDENTITY → GENERATED BY DEFAULT AS IDENTITY on modern PostgreSQL, rather than the older serial approach.

T-SQL with no direct equivalent

Expect to rewrite rather than translate:

• •TOP n → LIMIT n, and TOP inside subqueries often needs restructuring.
• •ISNULL → COALESCE. NULLIF behaves the same on both, that one is safe.
• •GETDATE() → now() or CURRENT_TIMESTAMP, with time zone semantics to think about if you move to timestamptz.
• •String concatenation with + → ||, and note that + silently does arithmetic when both sides look numeric.
• •DATEADD / DATEDIFF → interval arithmetic, which reads differently and rounds differently.
• •MERGE → INSERT ... ON CONFLICT for the common upsert case.
• •Temp tables (#tmp) → TEMP TABLE, with different visibility and lifetime rules.
• •Table-valued parameters → arrays, JSON payloads, or a staging table.

Stored procedures and triggers

A SQL Server procedure can simply SELECT and the caller receives a result set. PostgreSQL does not work that way: you need a function with RETURNS TABLE, or a refcursor, and the calling code changes with it. Any procedure returning multiple result sets needs redesigning, not porting.

Triggers differ structurally too. SQL Server gives you statement-level triggers with inserted and deleted pseudo-tables; PostgreSQL gives you row-level triggers with NEW and OLD, and the trigger body lives in a separate function. A trigger written to process a batch will behave differently, and usually a great deal more slowly, when it fires once per row.

The parts that are not the database

This is where estimates go wrong. The database itself is maybe half the work.

• •SQL Agent jobs. There is no equivalent. They become pg_cron, systemd timers, or whatever scheduler you already run — and they need logging and alerting they previously inherited for free.
• •SSRS and SSIS. Reports and ETL packages do not port. They get rebuilt or replaced, and that is frequently a separate project with its own budget.
• •Linked servers. Cross-database queries need postgres_fdw, or the architecture changes.
• •The application layer. Connection strings, drivers, ORM dialect, and every piece of raw SQL in the codebase. If an ORM does the heavy lifting this is lighter; if hand-written T-SQL is scattered through the application, it is not.
• •Backup and monitoring. Existing tooling probably does not speak PostgreSQL. pgBackRest and a monitoring stack need to be in place before cutover, not after.

Tools worth knowing

• •pgloader — handles schema and data in one pass with sensible type mapping, and is the fastest way to get a first migration you can actually test against.
• •AWS Schema Conversion Tool — useful for the assessment report even if you are not going to AWS. It flags what it cannot convert automatically, which is precisely the list you want.
• •Babelfish for Aurora PostgreSQL — speaks the SQL Server wire protocol and a large T-SQL subset. Worth evaluating if rewriting the application is genuinely off the table, with the caveat that it ties you to a specific platform.

No tool converts business logic correctly without review. Treat automated output as a first draft that a person reads line by line.

Order of work

• •Inventory. Every table, procedure, function, trigger, view, job, report and external consumer. Nothing else starts before this list exists.
• •Decide collation and type mappings explicitly, and write the decisions down.
• •Convert the schema, review it by hand, and load a full copy of the data into a test instance.
• •Point a copy of the application at it and run the real workload, not a smoke test.
• •Validate: row counts per table, checksums or aggregates on the columns that matter, and targeted spot-checks on queries most likely to be affected by collation.
• •Run both systems in parallel long enough to cover the monthly and quarterly jobs — those are the ones that surface after go-live otherwise.
• •Cut over during a window you can afford to reverse.

Have a rollback plan someone has tested

The plan is not “we keep the old server around.” It is: how long can the old system stay authoritative, what happens to writes that landed in PostgreSQL after cutover, and who decides to pull the trigger. If the answer to the second question is that those writes would be lost, then the cutover window has to be short enough that losing them is acceptable — and everyone should agree on that in advance, not at three in the morning.

Rough shape of the effort

A small application with a straightforward schema, an ORM generating most queries and few stored procedures can be done in a couple of weeks. A system with hundreds of procedures, SSRS reports, SQL Agent jobs and raw T-SQL threaded through the application is a multi-month project in which the database is the easy part. The inventory step is what tells you which one you have, and it takes days rather than weeks — there is no good reason to skip it.

If you want a second opinion

We do MSSQL and PostgreSQL work as a service: assessments, migrations with rollback planning, high availability with Patroni, and ongoing DBA support. If you are weighing up a migration and want someone to walk the inventory with you before committing, that is a fixed-scope engagement — and we will say so if the honest answer is that you should stay where you are.