blog

Migrating from MSSQL to PostgreSQL – What You Should Know

Severalnines AC

Published

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
Download the Whitepaper Today
 
PostgreSQL Management & Automation with ClusterControl
Learn about what you need to know to deploy, monitor, manage and scale PostgreSQL

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:

DATEPART( datepart , date )

PostgreSQL:

date_part( text , timestamp )
date_part( text , interval )

ISNULL

ISNULL function must be replaced by COALESCE function in PostgreSQL.

Example

MS SQL Server:

ISNULL(exp, replacement)

PostgreSQL:

COALESCE(exp, replacement)

SPACE

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

Example

MS SQL Server:

SPACE($n)

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

PostgreSQL:

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:

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

PostgreSQL:

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

SELECT FirstName + LastName FROM employee;

PostgreSQL:

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:

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

PostgreSQL:

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:

SELECT GETDATE();

PostgreSQL:

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.

Subscribe below to be notified of fresh posts