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.
'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.
CHARINDEX
must be reiplaced by PostgreSQL
equivalent POSITION
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 |
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 |
DATEPART
must be replaced by DATE_PART
GETDATE
must be converted into PostgreSQL NOW()
ISNULL
must be replaced by COALESCE
REPLICATE
must be converted into PostgreSQL equivalent
REPEAT
SPACE($n)
must be replaced by REPEAT(' ', $n)