Migrating from MSSQL to PostgreSQL - What You Should Know

As you may know, Microsoft SQL Server is very popular RDBMS with highly restrictive licencing and high cost of ownership if the database is of significant size, or is used by a significant number of clients. It provides a very user-friendly interface and easy to learn. This has resulted in a large installed user base.

PostgreSQL is the world's most advanced open source database. The PostgreSQL community is very strong and continuously improving existing features and implementing new features. As per db-engine popularity rank, PostgreSQL was the DBMS of the year 2017.

Why Migrate from MS SQL Server to PostgreSQL?

1. MS SQL Server is a proprietary database from Microsoft, while PostgreSQL is developed and maintained by a global community of open source developers. If cost is an issue, then definitely you should go with PostgreSQL. You can check the pricing here.
2. PostgreSQL is a cross platform database engine and it is available for Windows, Mac, Solaris, FreeBSD and Linux while SQL Server only runs on Windows operating system. As you may know, PostgreSQL is open source and completely free while MSSQL Server cost depends on the number of users and database size.
3. Flexible open source licencing and easy availability from public cloud providers like AWS, Google cloud etc.
4. Benefit from open source add-ons to improve performance.

What You Should Know

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

Before migration, understand the differences between MSSQL and PostgreSQL. There are many features in both databases so you should know the behaviour of those features/functions in MSSQL and PostgreSQL. Please check some important differences you should know before migration.

Data Type Mapping

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

Please check the below table.

Microsoft SQL Server		PostgreSQL
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 <= n <= 8000	CHAR(n)
VARCHAR(n)	Variable length char string, 1 <= n <= 8000	VARCHAR(n)
VARCHAR(max)	Variable length char string, <= 2GB	TEXT
VARBINARY(n)	Variable length byte string , 1 <= n <= 8000	BYTEA
VARBINARY(max)	Variable length byte string , <= 2GB	BYTEA
NVARCHAR(n)	Variable length Unicode UCS-2 string	VARCHAR(n)
NVARCHAR(max)	Variable length Unicode UCS-2 data, <= 2GB	TEXT
TEXT	Variable length character data, <= 2GB	TEXT
NTEXT	Variable length Unicode UCS-2 data, <= 2GB	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 with fraction	TIMESTAMP(3)
DATETIME2(p)	Date and Time with fraction	TIMESTAMP(n)
DATETIMEOFFSET(p)	Date and Time with fraction and time zone	TIMESTAMP(p) WITH TIME ZONE
SMALLDATETIME	Date and Time	TIMESTAMP(0)
TINYINT	8 bit unsigned integer, 0 to 255	SMALLINT
UNIQUEIDENTIFIER	16 byte GUID(UUID) data	CHAR(16)
ROWVERSION	Automatically updated binary data	BYTEA
SMALLMONEY	32 bit currency amount	MONEY
IMAGE	Variable length binary data, <= 2GB	BYTEA

 

Incompatibilities in MS SQL Server and PostgreSQL

 

There are many incompatibilities present in MS SQL Server and PostgreSQL, You can see some of them here. You can automate them by creating extensions so that you can use the MS SQL Server function as it is in PostgreSQL and you can save your time.

DATEPART

DATEPART must be replaced by DATE_PART in PostgreSQL.

Example

MS SQL:

1	DATEPART( datepart , date )

PostgreSQL:

1 2	date_part( text , timestamp ) date_part( text , interval )

ISNULL

ISNULL function must be replaced by COALESCE function in PostgreSQL.

Example

MS SQL Server:

1	ISNULL(exp, replacement)

PostgreSQL:

1	COALESCE(exp, replacement)

SPACE

SPACE function in MS SQL Server must be replaced by REPEAT function in PostgreSQL.

Example

MS SQL Server:

1

SPACE($n)

Where $n is the number of spaces to be returned.

PostgreSQL:

1	REPEAT(‘ ’, $n)

DATEADD

PostgreSQL does not provide DATEADD function similar to MS SQL Server, you can use datetime arithmetic with interval literals to get the same results.

Example

MS SQL Server:

1 2	--Add 2 day to the current date SELECT DATEADD(day, 2, GETDATE());

PostgreSQL:

1 2	--Add 2 day to the current date SELECT CURRENT_DATE + INTERVAL ‘2 day’;

String Concatenation

MS SQL Server uses ‘+’ for String Concatenation whereas PostgreSQL uses ‘||’ for the same.

Example

MS SQL Server:

1	SELECT FirstName + LastName FROM employee;

PostgreSQL:

 

1	SELECT FirstName || LastName FROM employee;

CHARINDEX

There is CHARINDEX function in PostgreSQL. You can replace this function by PostgreSQL equivalent POSITION function.

Example

MS SQL Server:

1	SELECT CHARINDEX('our', 'resource');

PostgreSQL:

1	SELECT POSITION('our' in 'resource');

GETDATE

GETDATE function returns the current date and time. There is no GETDATE function in PostgreSQL, but there is NOW() function for the same purpose. If there are multiple occurrences of the GETDATE function then you can automate them using extension. Please check how to create modules using extension.

Example

MS SQL Server:

1	SELECT GETDATE();

PostgreSQL:

1	SELECT NOW();

Tools

You can use some tools to migrate MS SQL Server database to PostgreSQL. Please test the tool before use it.

1. Pgloader

   You can use the pgloader tool to migrate MS SQL database to PostgreSQL. The commands in the pgloader load the data from MS SQL database. Pgloader supports automatic discovery of the schema, including build of the indexes, primary key and foreign keys constraints.

   Pgloader provides various casting rules which can convert the MS SQL data type to a PostgreSQL data type.

2. Sqlserver2pgsql

   This is another open source migration tool to convert Microsoft SQL Server database into a PostgreSQL database, as automatically as possible. Sqlserver2pgsql is written in Perl.

   Sqlserver2pgsql tool does two things:

   1. It converts a SQL Server schema to a PostgreSQL schema
   2. It can produce a Pentaho Data Integrator (Kettle) jib to migrate all the data from SQL Server to PostgreSQL. This is an optional part.

Testing

Testing the application and migrated database is very important because some of the functions are the same in both the databases, however, the behaviour is different.

Some common scenarios need to be checked:

• Check whether all database objects are correctly converted or not.
• Check the behaviour of all the functions in DML is working correctly or not.
• Load sample data into both databases and check the result of all the DML queries in both the databases. The result of all the SQL’s should be the same.
• Check the performance of the DML and improve it if necessary.