Skip links

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.

SQL ServerDescriptionPostgreSQL Server
BIGINT64-bit integerBIGINT
BINARY(n)Fixed length byte stringBYTEA
CHAR(n)Fixed length char string, 1
VARCHAR(n)Variable length char string, 1
VARCHAR(max)Variable length char string,
VARBINARY(n)Variable length byte string , 1
NVARCHAR(n)Variable length Unicode UCS-2 stringCHARACTER VARYING(n)
NVARCHAR(max)Variable length Unicode UCS-2 data,
DOUBLE PRECISIONDouble precision floating point numberDOUBLE PRECISION
FLOAT(p)Floating point numberDOUBLE PRECISION
INTEGER32 bit integerINTEGER
NUMERIC(p,s)Fixed point numberNUMERIC(p,s)
DATEDate includes year, month and dayDATE
DATETIMEOFFSETDate and Time with fraction and time zoneTIMESTAMP WITH TIME ZONE
TINYINT8 bit unsigned integer, 0 to 255SMALLINT
ROWVERSIONAutomatically updated binary dataBYTEA
IMAGEVariable length binary data,
INT IDENTITY(1,1)Integer Number with Auto IdentityBIGSERIAL

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

DescriptionSQL ServerPostgreSQL 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";

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

Return to top of page

Inquiry Form

Inquiry Form