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. SQL Server queries often use FOR XML PATH syntax pattern to merge fields into comma separated string:

   SELECT distinct r.price, STUFF(
   	(SELECT distinct ','+ Cast(a.code as varchar) FROM tbl_price a 
   	WHERE r.price = a.price FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '') 
   FROM tbl_price r
   

   Those queries must be rewritten using STRING_AGG in PostgreSQL:

   SELECT price, string_agg(DISTINCT code::varchar,',') code
   FROM tbl_price
   GROUP BY price
   

8. Pivot tables require special attention as PostgreSQL does not support this feature directly.

Have questions? Contact us