Migrate from Microsoft SQL to PostgreSQL

      Comments Off on Migrate from Microsoft SQL to PostgreSQL

In order to reduce cost of ownership and take benefits of open-source software, a lot of organizations migrate their databases from SQL Server to PostgreSQL as it is the most advanced open source database. Despite of the fact that both DBMS are ANSI-SQL compliant, there are some differences in SQL syntax, data types, case sensitivity and built-in functions. Those differences makeMicrosoft SQL to PostgreSQL database migration not so trivial.It is very important to recognize potential issues before starting the migration procedure.

Data types

Although MS SQL and PostgreSQL have similar sets of data types, some of them don’t match and so must be mapped properly. Here is the table illustrating appropriate mapping:

SQL Server PostgreSQL Description
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 16 byte GUID(UUID) data
VARBINARY(n) BYTEA Variable length binary data
VARCHAR(max) TEXT Variable length char string

Built-in functions

Both SQL Server and PostgreSQL use built-in functions in queries, stored procedures and functions. The table below contains primary differences between functions in two DBMS:

SQL Server PostgreSQL Description
CHARINDEX POSITION Search substring in string
DATEADD operator ‘+’ Add interval to date
DATEPART DATE_PART Extract part of the date
GETDATE NOW Get current system date
ISNULL COALESCE Replace NULL by the specified expression
REPLICATE REPEAT Generate string as replication of the specified symbol
SPACE(n) REPEAT(‘ ‘, n) Generate string as replication of the space symbol

Finally, there are few differences in naming, default schemas and case sensitivity that must be handled correctly as well:

  • MS SQL uses square brackets around object names to allow composed names or keyword, PostgreSQL uses double quotes for the same purposes
  • default MS SQL schema is “dbo” while in PostgreSQL it is “public”
  • unlike SQL Server, PostgreSQL is case sensitive. All names must be changed to lower case in order to avoid collisions.

This whitepaper illustrates there are many aspects in MS SQL to PostgreSQL database migration that require special attention and careful processing. Manual migration may cause a lot of problems due to human errors. This is the reason why most of database specialists use special software to automate migrating procedure.

More information about database migration from Microsoft SQL to PostgreSQL can be found at http://www.convert-in.com/docs/mss2pgs/intro.htm