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.

SQL Server	Description	PostgreSQL Server
BIGINT	64-bit integer	BIGINT
BINARY(n)	Fixed length byte string	BYTEA
BIT	1, 0 or NULL	BOOLEAN
CHAR(n)	Fixed length char string, 1	CHAR(n)
VARCHAR(n)	Variable length char string, 1	CHARACTER VARYING(n)
VARCHAR(max)	Variable length char string,	TEXT
VARBINARY(n)	Variable length byte string , 1	BYTEA
NVARCHAR(n)	Variable length Unicode UCS-2 string	CHARACTER VARYING(n)
NVARCHAR(max)	Variable length Unicode UCS-2 data,	TEXT
DOUBLE PRECISION	Double precision floating point number	DOUBLE PRECISION
FLOAT(p)	Floating point number	DOUBLE PRECISION
INTEGER	32 bit integer	INTEGER
NUMERIC(p,s)	Fixed point number	NUMERIC(p,s)
DATE	Date includes year, month and day	DATE
DATETIME	Date and Time	TIMESTAMP WITHOUT TIME ZONE
DATETIMEOFFSET	Date and Time with fraction and time zone	TIMESTAMP WITH TIME ZONE
TINYINT	8 bit unsigned integer, 0 to 255	SMALLINT
UNIQUEIDENTIFIER	16 byte GUID(UUID) data	UUID OR CHAR(16)
ROWVERSION	Automatically updated binary data	BYTEA
IMAGE	Variable length binary data,	BYTEA
INT IDENTITY(1,1)	Integer Number with Auto Identity	BIGSERIAL

There are many incompatibilities present in SQL Server and PostgreSQL, You can see some of them here. Incompatibilities in MS SQL Server and PostgreSQL :

Description	SQL Server	PostgreSQL Server
Assign value to variable.	VARIABLE = 5;	“VARIABLE” := 5;
Get specified part of a date.	DATEPART( datepart , date )	DATE_PART( text , timestamp )
Current Date and Time.	GETDATE()	NOW()
Returns a specified value if the expression is NULL.	ISNULL(exp, replacement)	COALESCE(exp, replacement)
Adds a time/date interval to a date.	DATEADD(day, 2, GETDATE());	NOW() + INTERVAL ‘2 day’;
String Concatenation.	SELECT FirstName + LastName FROM TableName;	SELECT FirstName || LastName FROM TableName;
Searches a Substring.	CHARINDEX('our', 'resource')	POSITION('our' in 'resource');
Get specified part of a date.	DATENAME(mm, GETDATE());	TO_CHAR(NOW(), 'Month');
Creates the Unique Value.	NEWID();	CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; uuid_generate_v4();

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