Last updated: 2026-06-14

Finish a PostgreSQL schema after migrating from SQL Server

Migrating your SQL Server tables to PostgreSQL gets you the hard 90% — every table, type, key, index, and row. This guide covers the rest: turning that table-complete database into a finished PostgreSQL schema, with the views, routines, and triggers ported and the data verified. It's the checklist for the day after the migration.

If you haven't run the migration yet, start with Migrate SQL Server to PostgreSQL (or the cross-engine database migration overview for other engine pairs). Come back here once your tables and data are in PostgreSQL.

What the migration already did

So you know what not to redo, here's the full mechanical scope the migration covers automatically:

• Tables and columns with types mapped through a canonical model (nvarchar→varchar, bit→boolean, datetime2→timestamp, uniqueidentifier→uuid, and the rest — see the type mapping reference).
• Identity as GENERATED AS IDENTITY, with the backing sequence reset after load so the next id continues correctly.
• Defaults, CHECK and UNIQUE constraints, primary keys, and foreign keys with their ON DELETE / ON UPDATE actions (added in a second pass so table order never matters).
• Indexes, including filtered (partial) and INCLUDE covering indexes.
• Computed columns as GENERATED ... STORED where the expression is portable.
• All the row data, with value coercion (a bit 0/1 lands as boolean, binary as bytea, and so on).

Everything in that list is already correct in PostgreSQL. Don't rewrite it — the remaining work is procedural.

Step 1 — Get the to-do list with cross-engine Schema Compare

Open Schema Compare with your SQL Server database as the source and the migrated PostgreSQL database as the target. Because the two are on different engines, it runs in cross-engine mode: tables read as identical (they were migrated faithfully), and the objects that still need attention show up as only in source:

• Views present on SQL Server, missing on PostgreSQL.
• Stored procedures and functions not yet ported.
• Triggers — easy to forget, because they're attached to tables rather than listed on their own.

That list is your checklist, and it shrinks as you port each object. Cross-engine compare reports these by name presence only — it never shows a body "diff", because a T-SQL body and a PL/pgSQL body aren't comparable. If you generate a sync script across engines, these objects come through as -- [MANUAL PORT] notes rather than invalid SQL.

Step 2 — Port each procedural object

For each object on the list, script the original as CREATE in one tab, open a PostgreSQL query editor beside it, and rewrite the body. The dialect references do the heavy lifting:

• T-SQL vs PL/pgSQL — variables, control flow, error handling, temp tables.
• T-SQL → PostgreSQL function cheat sheet — every built-in that changes name.
• Converting stored procedures — a worked example with OUTPUT params and result sets.
• Converting triggers — INSERTED/DELETED → NEW/OLD and transition tables.

A practical order: views first (other objects may depend on them), then functions, then procedures, then triggers last (they reference tables and functions that should already exist).

Step 3 — Review the migration warnings

The migration's preview and report flag anything it couldn't carry cleanly — it warns by name rather than guessing. Revisit each:

• Unmapped types. SQL Server xml, sql_variant, spatial (geography/geometry), and CLR types have no canonical equivalent and were emitted verbatim with a warning. Decide the PostgreSQL target — xml maps to xml, spatial wants PostGIS, sql_variant usually becomes jsonb or text.
• Non-portable defaults. A default expression that uses a SQL Server function (GETDATE(), NEWID()) is flagged; replace it with the PostgreSQL equivalent (now(), gen_random_uuid()) on the migrated column.
• Collation. Per-column collations aren't carried; set them on the PostgreSQL side if your sort/compare semantics depend on them.

Step 4 — Verify the data

Row counts converging is a good sign but not proof the values are right. After a single-table migration, use Verify with Data Compare — it opens a cross-engine Data Compare between the SQL Server source and the migrated PostgreSQL table and compares row by row, so a coercion that went wrong (a date that shifted, a boolean that inverted) is caught immediately. For reference and lookup tables especially, this is the step that turns "it ran" into "it's correct".

Re-running safely

If you migrate a whole database and some tables already exist on the target, the migration skips them (and lists which it skipped) rather than overwriting — so a re-run only fills gaps. That makes the workflow iterative: migrate tables, port some procedures, migrate a few more tables a colleague added, and nothing you've already finished gets clobbered.

The finished-schema checklist

1. ✅ Tables, types, keys, indexes, and data — done by the migration.
2. ☐ Run a cross-engine Schema Compare for the object inventory.
3. ☐ Port views → functions → procedures → triggers, using the dialect references.
4. ☐ Resolve each migration warning (unmapped types, non-portable defaults, collation).
5. ☐ Verify data with Data Compare, especially reference tables.

Work down the list and the cross-engine compare goes quiet — at which point your PostgreSQL schema is genuinely finished, not just table-complete.

Frequently asked questions