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