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