Being a powerful and popular relational database management system, SQL Server has high cost of licensing, especially for large databases and number of workplaces. This is the primary reason of database migration from SQL Server to Postgres for many organizations. They choose PostgreSQL as an advanced open source DBMS fully compatible with SQL92 standard that has even more capabilities and advanced features than SQL Server.
It is very important to recognize all differences between MS SQL and PostgreSQL before migration.
Types Conversion
Although SQL Server and PostgreSQL support similar sets of data types, some of those types don’t match and consequently must be converted from the source to the target system properly. Here is the table of safe type mappings:
Microsoft SQL | PostgreSQL | Comments |
BINARY(n) | BYTEA | Binary data |
BIT | BOOLEAN | 1, 0 or NULL |
DATETIME | TIMESTAMP(3) | Date and Time with fraction |
FLOAT(p) | DOUBLE PRECISION | FLOAT(p) |
IMAGE | BYTEA | Variable length binary data |
INT IDENTITY | SERIAL | Auto-increment column |
NVARCHAR(max) | TEXT | Variable length Unicode data |
TINYINT | SMALLINT | 8 bit unsigned integer, 0 to 255 |
UNIQUEIDENTIFIER | UUID | Unique identifier |
VARBINARY(n) | BYTEA | Binary data of variable length |
VARCHAR(max) | TEXT | Text data of variable length |
Built-in Functions
SQL Server and PostgreSQL provide rich collections of built-in functions for manipulations over the data, conversion between different data formats and other common tasks. These functions are used in views, stored procedures and functions and so must be properly converted into PostgreSQL equivalents to preserve database logic. See below equivalents for SQL Server functions missing in PostgreSQL :
Microsoft SQL | PostgreSQL | Comments |
CHARINDEX | POSITION | Locates substring in string |
DATEADD | operator ‘+’ | Adds interval to date |
DATEPART | DATE_PART | Extracts part of the date |
GETDATE | NOW | Returns current system date |
ISNULL | COALESCE | Replaces NULL values by the specified expression |
REPLICATE | REPEAT | Returns replication of the specified symbol |
SPACE(n) | REPEAT(‘ ‘, n) | Returns replication of space symbol |
Also, there is difference in string concatenation operator in SQL Server and PostgreSQL: SQL Server uses ‘+’ for String Concatenation while PostgreSQL uses ‘||’ for the same purpose.
Besides the challenges specified above, these two DBMS have different restrictions on object names (tables, columns, indxes, etc), as well as default schemas and case sensitivity rules. There are workarounds for every such issue:
- Just like SQL Server encloses object names in square brackets for space symbols or keyword, PostgreSQL uses double quotes for the same purposes
- Unlike SQL Server, Postgres object names are case sensitive. It is recommended to convert all names to lower case to avoid collisions.
- In SQL Server default schema is “dbo”, in PostgreSQL it is “public”
The transformations and workarounds listed above make SQL Server to Postgres database migration a tedious and complicated procedure. To avoid risk of data loss or corruption experienced database administrators use special software to automate migration process. Some of these tools are listed below.
Pgloader
This is free tool for SQL Server to Postgres database migration providing automatic conversion of schemas, building indexes, primary key and foreign keys constraints. Also, pgloader allows to customize casting rules to make conversion from SQL Server to PostgreSQL even more flexible.
Sqlserver2pgsql
Another open source solution for SQL Server to Postgres database migration is Perl script called Sqlserver2pgsql. This tool provides the following capabilities:
- convert SQL Server schema into PostgreSQL schema
- create Pentaho Data Integrator (Kettle) jib to migrate all the data from SQL Server to Postgres
Besides free converters that usually provide just a partial automation of the database migration, there are commercial software capable of automating the entire SQL Server to Postgres database migration. Visit https://www.convert-in.com/mss2pgs.htm for learn more about one of such database migration tools.