How to Migrate From SQL server to PostGreSQL

As a Software development organization, we do get a lot of requests in the migration project. They are due to various reasons such as technology up-gradation, performance enhancement, and cost optimization. And for an application database is always going to be the backbone.

When a request for database migration arises a lot more things need to be verified and planned before moving ahead with such requests.

One of the similar request that we received allowed us to deep dive into this and am sharing my observations here.

SQL Server a very well known and famous Relational Database Management System (RDBMS). It’s the most popular RDBMS within the developer’s community.

PostgreSQL is an open-source database. Its community is growing powerful, strong and continuously implementing new features and improving existing features. PostgreSQL was the DBMS of the year in 2017.

Why Migrate from MS SQL Server to PostgreSQL? :

1. SQL Server is a license database from Microsoft, while PostgreSQL is developed and maintained by a global community of open source developers.
2. PostgreSQL is open source and completely free while MS-SQL Server cost depends on the number of users and database size.
3. Benefit from open source add-ons to improve performance.

What You Should Know :

Although both SQL Server database and PostgreSQL database are ANSI-SQL compliant but there are some differences between syntax, data-types, case sensitivity and it makes transferring data not so trivial.

PostGreSQL is case sensitive. Please check some important differences before the migration.

Data Type Mapping :

Some of the data types of SQL doesn’t match directly with PostgreSQL data types, so you need to change it to corresponding PostgreSQL data type.

Please check the below table.

[table id=2 /]
There are many incompatibilities present in SQL Server and PostgreSQL, You can see some of them here. Incompatibilities in MS SQL Server and PostgreSQL :

[table id=1 /]

These are a few prerequisites that a developer needs to take care of while planning to move from MS-SQL to PostGRE SQL.

Author: Parth Talaviya