Disclosure: I'm the author of dbSlice, a Windows tool, and I use it in the example. The five details below apply to any tool, and to a hand-written script too.
When an application moves from SQL Server to PostgreSQL, the schema usually gets the attention. The data is where the surprises are.
1. datetimeoffset loses its offset
A common mapping is TIMESTAMP, with values written in UTC. If the application needs the time zone, declare the column TIMESTAMPTZ in the CREATE TABLE before the load.
2. bit becomes BOOLEAN
1 and 0 arrive as true and false. Every query that compares with = 1 has to change.
3. nvarchar(max), ntext and xml
Use TEXT. PostgreSQL text is Unicode, so there is no N prefix. Declare XML yourself if you query it as XML.
4. rowversion / timestamp is not a date
It is binary in SQL Server (BYTEA in PostgreSQL). Leave it out of the copy unless the application reads it.
5. Case and collation
SQL Server usually compares text case-insensitively; PostgreSQL does not by default. Plan ILIKE, citext or a case-insensitive collation wherever the application relies on it.
How I check it
-
Create the target tables from reviewed DDL. dbSlice does not create anything in your database by default: if the PostgreSQL table is missing it stops and shows a
CREATE TABLEtranslated from SQL Server, for you or your DBA to review and run. That is where fixes 1 to 4 go. -
Initial load, then a nightly upsert by key (use a column that only grows, like
updated_ator theIDENTITYid) so PostgreSQL stays in step until cutover. -
Fix the sequences. After rows are copied with their
IDENTITYvalues, the PostgreSQL sequence can be behind the highest id. The tool can move it past the max id, or show the command. - Compare key by key before the switch. The compare saves the list of divergent keys to a file.
What it does not do
It moves data, not T-SQL: stored procedures, triggers and views have to be rewritten for PostgreSQL. Incremental runs do not detect deletes at the source, so compare or reload before cutover. No CDC or SQL Server Agent is needed: it reads with ordinary queries.
Try it
There is a free version on the Microsoft Store (connects, browses, compares, exports CSV). Writing to the target needs a license.
- Guide: https://dbslice.net/migrate/sql-server-to-postgresql
- Nightly sync routine: https://dbslice.net/sync/sql-server-to-postgresql
- Comparing tools (pgloader and others): https://dbslice.net/compare/sql-server-to-postgresql-migration-tools
- Free version: https://apps.microsoft.com/detail/9PMS69X1T53Z
Which of these has bitten you? I'm happy to answer questions about the type mapping.

Top comments (0)