Database Migration from MS SQL to PostgreSQL   

When migrating a database from SQL Server to PostgreSQL, it is important to recognize differences between the two database management systems. This whitepaper covers main aspects of conversion database entries between MSSQL and PostgreSQL.

Types

Both SQL Server and PostgreSQL support basic types of SQL standard, however there are some minor differences that should be handled properly. The table of valid MS SQL to PostgreSQL type conversions is:

Microsoft SQL  PostgreSQL 
BINARY(n) BYTEA
BIT BOOLEAN
DATETIME TIMESTAMP(3)
FLOAT(p) DOUBLE PRECISION
IMAGE BYTEA
INT IDENTITY SERIAL
NVARCHAR(max)             TEXT
TINYINT SMALLINT
UNIQUEIDENTIFIER UUID
VARBINARY(n) BYTEA
VARCHAR(max) TEXT

 

Built-in Functions

Built-in functions are used in SELECT statements, views, stored procedures and functions. Some of these functions are the same in SQL Server and PostgreSQL while others are not. PostgreSQL equivalents of missing MS SQL functions are listed below:

Microsoft SQL PostgreSQL 
CHARINDEX POSITION
DATEADD operator ‘+’
DATEPART DATE_PART
GETDATE NOW
ISNULL COALESCE
REPLICATE REPEAT
SPACE(n) REPEAT(‘ ‘, n)

 

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

MS SQL and PostgreSQL have distinguished rules of naming for database entries management, different default schemas and case sensitivity. Corresponding workarounds for each issue are listed below:

  • SQL Server expects that object names containing space symbols or keyword are enclosed in square brackets. PostgreSQL encloses names of database entries in double quotes for the same purpose.
  • SQL Server default schema is “dbo”,while PostgreSQL it is “public”. All entries must be updated properly during the database migration.
  • In Postgre SQL database object names are case sensitive, in SQL Server they are not. So, all names must be converted to lower case when transferring from MS SQL to PostgreSQL in order to avoid collisions.

The updates listed above are necessary for MS SQL to PostgreSQL database migration and it makes the procedure too complicated for doing it manually. Many database specialists automate database migration using special software tools, scripts and APIs. Some of these solutions are explored below.

Pgloader

This is a free tool to migrate MS SQL database to PostgreSQL server. It provides conversion of schemas, indexes, primary keys and foreign keys constraints. Also, the tool supports user defined casting rules to customize types mapping.

Sqlserver2pgsql

This is a Perl script to convert Microsoft SQL Server database into a PostgreSQL format.It can convert SQL Server schema into PostgreSQL and create job for Pentaho Data Integrator framework to migrate all the data.

Commercial tools

Besides the two options listed above, there are some commercial tools that completely automate of MS SQL to PostgreSQL migration. One of these tools is MSSQL-to-PostgreSQL developed by Intelligent Converters. It converts schemas, data, indexes, constraints and views. The tool supports command line to automate and schedule the migration process.