When migrating a database from SQL Server to PostgreSQL, it is important to recognize differences between the two database management systems. This whitepaper covers main aspects of conversion database entries between SQL Server and PostgreSQL.
Types
Both SQL Server and PostgreSQL support basic types of SQL standard, however there are some minor differences that should be handled properly. The table of valid SQL Server to PostgreSQL type conversions is:
| SQL Server | PostgreSQL |
| BINARY(n) | BYTEA |
| BIT | BOOLEAN |
| DATETIME | TIMESTAMP(3) |
| FLOAT(p) | DOUBLE PRECISION |
| IMAGE | BYTEA |
| INT IDENTITY | SERIAL |
| NVARCHAR(max) | TEXT |
| TINYINT | SMALLINT |
| UNIQUEIDENTIFIER | UUID |
| VARBINARY(n) | BYTEA |
| VARCHAR(max) | TEXT |
Built-in Functions
Built-in functions are used in SELECT statements, views, stored procedures and functions. Some of these functions are the same in SQL Server and PostgreSQL while others are not. PostgreSQL equivalents of missing SQL Server functions are listed below:
| SQL Server | PostgreSQL |
| CHARINDEX | POSITION |
| DATEADD | operator ‘+’ |
| DATEPART | DATE_PART |
| GETDATE | NOW |
| ISNULL | COALESCE |
| REPLICATE | REPEAT |
| SPACE(n) | REPEAT(‘ ‘, n) |
Also, there is difference in string concatenation operator in two DBMS: SQL Server uses plus operator ‘+’ for string concatenation while PostgreSQL uses ‘||’ for the same purpose.
SQL Server and PostgreSQL have distinguished rules of naming for database entries management, different default schemas and case sensitivity. Corresponding workarounds for each issue are listed below:
- SQL Server expects that object names containing space symbols or keyword are enclosed in square brackets. PostgreSQL encloses names of database entries in double quotes for the same purpose.
- SQL Server default schema is “dbo”, while PostgreSQL it is “public”. All entries must be updated properly during the database migration.
- In PostgreSQL database object names are case sensitive, in SQL Server they are not. So, all names must be converted to lower case when transferring from MS SQL to PostgreSQL in order to avoid collisions.
The updates listed above are necessary for SQL Server to PostgreSQL database migration and it makes the procedure too complicated for doing it manually. Human factor may cause data loss or corruption that is not acceptable for the organization relying on their data. Many database specialists automate database migration using special software tools, scripts and APIs. Some of these solutions are explored below.
Pgloader
This is a free tool to migrate databases from SQL Server to PostgreSQL. It provides conversion of main database objects such as schemas, indexes, primary keys and foreign keys constraints. Also, the tool supports user defined casting rules to customize types mapping. Pgloader may become a good choice for experienced users filling comfortable with the command line interface.
Sqlserver2pgsql
This is a Perl script to convert SQL Server database into a PostgreSQL format. It can convert SQL Server schema into PostgreSQL and create job for Pentaho Data Integrator framework to migrate all the data. Just like previous option, it may be efficiently used by database administrators or developers who can easily integrate this tool into their own scripts or programs. End users having no skills on running scripts from the command line should look for easy to use solutions supplied with graphical user interface and comprehensive documentation.
Commercial tools
Besides the two options listed above, there are some commercial tools that completely automate SQL Server to PostgreSQL database migration. One of these tools is MSSQL-to-PostgreSQL developed by Intelligent Converters. It provides the following capabilities:
- works with all modern versions of SQL Server and PostgreSQL both cloud and on-premises
- migrates MS SQL schemas, data, sequences, indexes, constraints and views
- supports command line to automate and schedule the migration process
- provides option to merge or synchronize existing PostgreSQL database with SQL Server data
- allows preprocessing data through SELECT-queries before migration
- full customization of name, type and other attributes of every column in the target tables
Visit official site of Intelligent Converters to learn more about their tool to migrate SQL Server to PostgreSQL.
