One of the most important steps of database migration from Microsoft SQL server to PostgreSQL is processing views. Syntax of CREATE VIEW queries in MS SQL and PostgreSQL is similar but not the same. The primary challenge of conversion process is that these two DBMS have distinguished sets of built-in functions. Consequently, each CREATE VIEW statement must be converted into the appropriate equivalent before importing into the destination database. This article discovers the most important patterns of converting views from SQL Server to PostgreSQL.
1) There are embedded functions in MS SQL that have on direct equivalent in PostgreSQL. Such functions must be implemented manually as follows:
CREATE OR REPLACE FUNCTION datediff(date, date)
RETURNS int AS $$
SELECT $1 – $2
$$ LANGUAGE plpgsql;
/*
*/
CREATE OR REPLACE FUNCTION isdate(text) RETURNS INT AS $$
DECLARE x TIMESTAMP;
BEGIN
if ($1 is null) then return 0; end if;
x = $1::TIMESTAMP;
RETURN 1;
EXCEPTION WHEN others THEN
RETURN 0;
END;
$$ LANGUAGE plpgsql;
/*
*/
CREATE OR REPLACE FUNCTION isnumeric(text) RETURNS BOOLEAN AS $$
DECLARE x NUMERIC;
BEGIN
x = $1::NUMERIC;
RETURN TRUE;
EXCEPTION WHEN others THEN
RETURN FALSE;
END;
$$ LANGUAGE plpgsql;
/*
*/
CREATE OR REPLACE FUNCTION patindex(
pattern VARCHAR,
expression VARCHAR)
RETURNS INT AS $$
SELECT
COALESCE( STRPOS($2, (
SELECT( REGEXP_MATCHES( $2,'(‘ ||
REPLACE(
REPLACE(
TRIM( $1, ‘%’ ), ‘%’, ‘.*?’ ), ‘_’, ‘.’ ) || ‘)’) )[ 1 ] LIMIT 1 ) ), 0
);
$$ LANGUAGE plpgsql;
2) Function ISNULL($var, $expr) that returns $expr if $var is NULL in MS SQL, must be replaced by PostgreSQL equivalent COALESCE($var, $expr)
3) Function GETDATE() returns the current date/time in SQL Server, it must be replaced by NOW() in PostgreSQL
4) Function DATEADD($interval, $n, $datetime) is used to add specified interval to $datetime in SQL Server. PostgreSQL allows to implement the same using operator ‘+’ as follows:
If $interval=’day’ then SELECT ($datetime + $n * interval ‘1 day’)::date
If $interval=’month’ then SELECT ($datetime + $n * interval ‘1 month’)::date
If $interval=’year’ then SELECT ($datetime + $n * interval ‘1 year’)::date
5) SQL Server provides multiple functions to extract particular part of the date/time value such as DAY(), MONTH(), YEAR(). These functions must be replaced by DATE_PART() in PostgreSQL as follows:
YEAR(datetime) -> DATE_PART(‘year’, datetime)
MONTH(datetime) -> DATE_PART(‘month’, datetime)
DAY(datetime) -> DATE_PART(‘day’, datetime)
6) In SQL Server function CHARINDEX get position of substring in the specified string, it has direct equivalent in PostgreSQL called POSITION
The guide above indicated that converting MS SQL queries into PostgreSQL format is not an easy procedure. It is reasonable to use special tools to automate the conversion, especially for large databases with a lot of views. One of such migration tools is MS SQL to PostgreSQL converter offered by Intelligent Converters, software vendor working in database migration field more than 15 years. The tool handles more than 80% of all possible syntax constructions used in CREATE VIEW queries. The price for the product is $49 that includes lifetime license, 1-year subscription for updates and 24/7 support.
More details about MS SQL to PostgreSQL converter can be found on the official page: https://www.convert-in.com/mss2pgs.htm
