Menu

SQL Server to PostgreSQL: five details that break a data migration (and how to check them)
📰

SQL Server to PostgreSQL: five details that break a data migration (and how to check them)

DEV Community: postgres·dbSlice by Thinwork·14 minutes ago
#yFp2K20J
#devtopostgresql#devto#dev#postgresql#server
Reading 0:00
15s threshold

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.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.