From:       To:      

Home > Documentation > MS SQL to PostgreSQL

Convert SQL Server views into PostgreSQL format

[SQL Server to PostgreSQL Converter]  [About Migration]  [Types]

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.