Migrate from SQL Server to PostgreSQL

How to Migrate from SQL Server to PostgreSQL

Spread the love

Open-source software becomes extremely popular nowadays. Following this trend, a lot of organizations migrate their databases from SQL Server to PostgreSQL as it is the most powerful open source DBMS. This procedure can help to reduce cost of ownership and take advantages of ANSI SQL standard compliance system. Due to some differences in data types, case sensitivity and built-in functions MS SQL to PostgreSQL migration not so trivial as it may seem at first glance. It is vital to recognize all possible issues before beginning the migration procedure.

Types Conversion

SQL Server and PostgreSQL have similar data types, however some of them don’t match and must be converted 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

Built-in functions are used in queries, stored procedures and functions. The table below contains equivalents of those functions in SQL Server and 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

These two DBMS also have different restrictions on object names, default schemas and case sensitivity. All those aspects must be handled correctly as follows:

  • MS SQL encloses object names in square brackets to allow space symbols or keyword, PostgreSQL uses double quotes for the same purposes
  • The default schema in SQL Server is “dbo” while PostgreSQL defines it as “public”
  • PostgreSQL names are case sensitive while in MS SQL they are not. In view of this fact, all names must be converted to lower case to avoid collisions.

This guide explores primary challenges of MS SQL to PostgreSQL database migration. Running the procedure manually may cause data loss or corruption due to human errors. In order to eliminate those risks many database specialists use special software to automate migration process.

Visit http://www.convert-in.com/docs/mss2pgs/intro.htm for more information about database migration from Microsoft SQL to PostgreSQL

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.