Converting SQL Server Views to PostgreSQL

One of the most important steps of database migration from Microsoft SQL server to PostgreSQL is processing views. Syntax of CREATE VIEW queries in MS SQL and PostgreSQL is similar but not the same. The primary challenge of conversion process is that these two DBMS have distinguished sets of built-in functions. Consequently, each CREATE VIEW statement must be converted into the appropriate equivalent before importing into the destination database. This article discovers the most important patterns of converting views from SQL Server to PostgreSQL.

1) There are embedded functions in MS SQL that have on direct equivalent in PostgreSQL. Such functions must be implemented manually as follows:

5) SQL Server provides multiple functions to extract particular part of the date/time value such as DAY(), MONTH(), YEAR(). These functions must be replaced by DATE_PART() in PostgreSQL as follows:

YEAR(datetime) -> DATE_PART(‘year’, datetime)

MONTH(datetime) -> DATE_PART(‘month’, datetime)

DAY(datetime) -> DATE_PART(‘day’, datetime)

6) In SQL Server function CHARINDEX get position of substring in the specified string, it has direct equivalent in PostgreSQL called POSITION

The guide above indicated that converting MS SQL queries into PostgreSQL format is not an easy procedure. It is reasonable to use special tools to automate the conversion, especially for large databases with a lot of views. One of such migration tools is MS SQL to PostgreSQL converter offered by Intelligent Converters, software vendor working in database migration field more than 15 years. The tool handles more than 80% of all possible syntax constructions used in CREATE VIEW queries. The price for the product is $49 that includes lifetime license, 1-year subscription for updates and 24/7 support.