Migrate from SQL Server to Postgres
SQL Server is a powerful and popular DBMS yet it has high cost of ownership for large data or number of clients. This is the reason why many organizations migrate their databases from SQL Server to Postgres, advanced open source ANSI SQL database management system.
It is very important to recognize all differences between SQL Server and Postgres before migration.
Types Conversion
Although SQL Server and Postgres types are similar, some of them don’t match and must be mapped properly. Here is the table of correct conversions:
| 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
Both SQL Server and Postgres provide collections of built-in functions for using in SELECT statements, views, stored procedures and functions. Some of these functions are the same while others are not. See below PostgreSQL equivalents of missing MS SQL functions:
| 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: SQL Server uses ‘+’ for String Concatenation while Postgres uses ‘||’ for the same purpose.
Besides the aspects listed above, these two database management systems apply distinguished restrictions on object names, have different default schemas and case sensitivity rules. There are workarounds for every such issue:
- Just like MS SQL encloses object names in square brackets for space symbols or keyword, PostgreSQL uses double quotes for the same purposes
- In SQL Server default schema is “dbo”, in PostgreSQL it is “public”
- Unlike SQL Server, database object names are case sensitive in Postgres. In view of this fact, all names must be converted to lower case to avoid collisions.
Database Migration Tools
Those transformations make procedure of SQL Server to Postgres database migration tedious and complicated enough to avoid doing it manually. In order to eliminate risks of data loss or corruption many database specialists use special software to automate migration process. Some of these tools are listed below.
Pgloader
First free tool to migrate database from SQL Server to Postgres is pgloader. It supports automatic conversion of schemas, building indexes, primary key and foreign keys constraints. Also, pgloader provides casting rules in order to customize conversion the SQL Server data type into a Postgres.
Sqlserver2pgsql
Another open source solution to convert Microsoft SQL Server database into a Postgres format is Perl script called Sqlserver2pgsql. This tool provides the following capabilities:
- convert SQL Server schema into Postgres schema
- create Pentaho Data Integrator (Kettle) jib to migrate all the data from SQL Server to Postgres
Besides the two options listed above, there are many commercial tools that provide full automation for the entire procedure of database migration from SQL Server to Postgres.
