• December 11, 2023

Migrate from SQL Server to Postgres

SQL Server is a powerful and popular DBMS yet it has high cost of ownership for large data or number of clients. This is the reason why many organizations migrate their databases from SQL Server to Postgres, advanced open source ANSI SQL database management system. 

It is very important to recognize all differences between SQL Server and Postgres before migration.

Types Conversion

Although SQL Server and Postgres types are similar, some of them don’t match and must be mapped properly. Here is the table of correct conversions: 

Microsoft SQL PostgreSQL Comments
BINARY(n)BYTEABinary data
BITBOOLEAN1, 0 or NULL
DATETIMETIMESTAMP(3)Date and Time with fraction
FLOAT(p)DOUBLE PRECISIONFLOAT(p)
IMAGEBYTEAVariable length binary data
INT IDENTITYSERIALAuto-increment column
NVARCHAR(max)TEXTVariable length Unicode data
TINYINTSMALLINT8 bit unsigned integer, 0 to 255
UNIQUEIDENTIFIERUUIDUnique identifier
VARBINARY(n)BYTEABinary data of variable length
VARCHAR(max)TEXTText data of variable length

Built-in Functions

Both SQL Server and Postgres provide collections of built-in functions for using in SELECT statements, views, stored procedures and functions. Some of these functions are the same while others are not. See below PostgreSQL equivalents of missing MS SQL functions: 

Microsoft SQLPostgreSQL Comments
CHARINDEXPOSITIONLocates substring in string
DATEADDoperator ‘+’Adds interval to date
DATEPARTDATE_PARTExtracts part of the date
GETDATENOWReturns current system date
ISNULLCOALESCEReplaces NULL values by the specified expression
REPLICATEREPEATReturns replication of the specified symbol
SPACE(n)REPEAT(‘ ‘, n)Returns replication of space symbol

Also, there is difference in string concatenation operator: SQL Server uses ‘+’ for String Concatenation while Postgres uses ‘||’ for the same purpose.

Besides the aspects listed above, these two database management systems apply distinguished restrictions on object names, have different default schemas and case sensitivity rules. There are workarounds for every such issue: 

  • Just like MS SQL encloses object names in square brackets for space symbols or keyword, PostgreSQL uses double quotes for the same purposes
  • In SQL Server default schema is “dbo”, in PostgreSQL it is “public”
  • Unlike SQL Server, database object names are case sensitive in Postgres. In view of this fact, all names must be converted to lower case to avoid collisions. 

Database Migration Tools

Those transformations make procedure of SQL Server to Postgres database migration tedious and complicated enough to avoid doing it manually. In order to eliminate risks of data loss or corruption many database specialists use special software to automate migration process. Some of these tools are listed below.

Pgloader

First free tool to migrate database from SQL Server to Postgres is pgloader. It supports automatic conversion of schemas, building indexes, primary key and foreign keys constraints. Also, pgloader provides casting rules in order to customize conversion the SQL Server data type into a Postgres.

Sqlserver2pgsql

Another open source solution to convert Microsoft SQL Server database into a Postgres format is  Perl script called Sqlserver2pgsql. This tool provides the following capabilities: 

  • convert SQL Server schema into Postgres schema
  • create Pentaho Data Integrator (Kettle) jib to migrate all the data from SQL Server to Postgres

Besides the two options listed above, there are many commercial tools that provide full automation for the entire procedure of database migration from SQL Server to Postgres.