Home > Documentation > MS SQL to PostgreSQL

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. If MS SQL 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.
2. Built-in function CHARINDEX must be reiplaced by PostgreSQL equivalent POSITION
3. Function DATEADD($interval, $n_units, $date) can be converted in PostgreSQL expressions that use operator '+' depending on $interval value as follows:

   SQL Server Interval	PostgreSQL Equivalent
   DAY/DD/D	($date + $n_units * interval '1 day')::date
   DAYOFYEAR/DY/Y	($date + $n_units * interval '1 day')::date
   HOUR/HH	($date + $n_units * interval '1 hour')::date
   MINUTE/MI/N	($date + $n_units * interval '1 minute')::date
   MONTH/MM/M	($date + $n_units * interval '1 month')::date
   QUARTER/QQ/Q	($date + $n_units * 3 * interval '1 month')::date
   SECOND/SS/S	($date + $n_units * interval '1 second')::date
   WEEK/WW/WK	($date + $n_units * interval '1 week')::date
   WEEKDAY/DW/W	($date + $n_units * interval '1 day')::date
   YEAR/YY	($date + $n_units * interval '1 year')::date

4. Function DATEDIFF($interval, $date1, $date2) of SQL Server can be emulated in PostgreSQL via DATE_PART as follows:

   SQL Server Interval		PostgreSQL Equivalent
   DAY/DD/D		date_part('day', $date2 - $date1)::int
   DAYOFYEAR/DY/Y		date_part('day', $date2 - $date1)::int
   HOUR/HH		24 * date_part('day', $date2 - $date1)::int + date_part('hour', $date2 - $date1)
   MINUTE/MI/N		1440 * date_part('day', $date2 - $date1)::int + 60 * date_part('hour', $date2 - $date1) + date_part('minute', $date2 - $date1)
   MONTH/MM/M		(12 * (date_part('year', $date2) - date_part('year', $date1))::int + date_part('month', $date2) - date_part('month', $date1))::int
   SECOND/SS/S		86400 * date_part('day', $date2 - $date1)::int + 3600 * date_part('hour', $date2 - $date1) + 60 * date_part('minute', $date2 - $date1) + date_part('second', $date2 - $date1)
   WEEK/WW/WK		TRUNC(date_part('day', $date2 - $date1) / 7)
   WEEKDAY/DW/W		date_part('day', $date2 - $date1)::int
   YEAR/YY		(date_part('year', $date2) - date_part('year', $date1))::int

5. Every occurrence of DATEPART must be replaced by DATE_PART
6. SQL Server function GETDATE must be converted into PostgreSQL NOW()
7. Every occurrence of ISNULL must be replaced by COALESCE
8. SQL Server function REPLICATE must be converted into PostgreSQL equivalent REPEAT
9. Every occurrence of SPACE($n) must be replaced by REPEAT(' ', $n)
10. Pivot tables require special attention as PostgreSQL does not support this feature directly.