Convert SQL Server views into PostgreSQL format

When migrate a database from SQL Server to PostgreSQL one of the steps is conversion of views into the destination format. This whitepaper discovers basic rules of this conversion.

1. all square brackets [] escaping SQL Server object names must be removed
2. If SQL Server query contains 'TOP (100) PERCENT' pattern, it must be removed in PostgreSQL query. If there is another value in CREATE VIEW statement, it can be replace by the following code in PostgreSQL:

   SELECT ... LIMIT N; 
   

   where N is number of rows to limit result of the query.
3. Logical function iif(condition, val1, val2) that returns one of two values depending on condition must be converted into

   CASE WHEN condition THEN val1 ELSE val2 END;
   

4. SQL Server default schema 'dbo' must be replaced by PostgreSQL equivalent 'public'
5. convert SQL Server built-in functions into PostgreSQL equivalents according to this guide

6. string concatenation operator string1 + string2 must be replaced by concat(string1,string2)
7. Pivot tables require special attention as PostgreSQL does not support this feature directly.

Have questions? Contact us