blog
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?
- 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.
- 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.
- Flexible open source licencing and easy availability from public cloud providers like AWS, Google cloud etc.
- 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:
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.
-
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.
-
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:
- It converts a SQL Server schema to a PostgreSQL schema
- 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.