Convert MySQL views into PostgreSQL format

Migration of MySQL database to PostgreSQL server involves converting MySQL views into the destination format and this part of the project may require essential manual efforts. Syntax of queries in MySQL and PostgreSQL is similar, yet not equal due to different sets of built-in functions.

Therefore, it is necessary to convert each of MySQL function into PostgreSQL equivalent before passing it to the destination DBMS. Here are the most important differences between MySQL and PostgreSQL.

• MySQL function curtime() must be replaced by current_time
• All occurrences of DAY($a) or DAYOFMONTH($a) must be converted into the following expression extract(day from date($a))::integer
• DATEDIFF($1, $2) must be replaced by expression $1 - $2
• DATE_FORMAT must be replaces by TO_CHAR, for example:

  date_format(NOW(),'%m-0-%Y') is converted into to_char(NOW(), 'mm-dd-yyyy')
  

• MySQL function HOUR($1) must be replaced by EXTRACT(hour FROM $1)::int
• All occurrences of pattern IF($a,$b,$c) are converted into CASE WHEN $a THEN $b ELSE $c END
• MySQL function IFNULL($a,$b) can be replaced by COALESCE($a,$b)
• INSTR($a, $b) can be replaced PostgreSQL equivalent position($b in $a)
• All occurrences of ISNULL($a) must be replaced by $a IS NULL
• MySQL function LCASE can be replaced PostgreSQL equivalent LOWER
• Simple two-arguments form of LOCATE ($a,$b) is equal to INSTR($a, $b) and so it can be converted into position($a in $b). More comprehensive porting of this function can be found here
• All occurrences of minute($1) must be replaced by expression EXTRACT(minute FROM $1)::int
• All occurrences of month($1) must be replaced by expression EXTRACT(month FROM $1)::int
• ORD($1) can be replaced PostgreSQL equivalent ASCII($1)
• MySQL function RAND() is converted into RANDOM()
• MySQL function SYSDATE() must be replaced by CURRENT_DATE
• All occurrences of UCASE are converted into PostgreSQL equivalent UPPER
• MySQL function WEEK($1) can be replaced by extract(week from ($1))::int
• All occurrences of YEAR($1) are converted into extract(year from $1)

Also, there are number of MySQL functions that cannot be converted to PostgreSQL by text replace. For this cases it would be reasonable to create these missing functions in the destination database:

CREATE OR REPLACE FUNCTION adddate(date, interval)
RETURNS date AS $$
SELECT ($1 + $2)::date; $$
LANGUAGE sql;

CREATE OR REPLACE FUNCTION _group_concat(text, text)
RETURNS text AS $$
  SELECT CASE
    WHEN $2 IS NULL THEN $1
    WHEN $1 IS NULL THEN $2
    ELSE $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2
  END
$$ IMMUTABLE LANGUAGE SQL;

DROP AGGREGATE IF EXISTS group_concat(text);
CREATE AGGREGATE group_concat (
	BASETYPE = text,
	SFUNC = _group_concat,
	STYPE = text
);

CREATE OR REPLACE FUNCTION left(str text, len int)
RETURNS text AS $$
SELECT substring($1 FROM 1 FOR $2)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION makedate(year int, dayofyear int)
RETURNS date AS $$
SELECT (date '0001-01-01' + ($1 - 1) * interval '1 year' + ($2 - 1) * interval '1 day'):: date
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION maketime(int, int, double precision)
RETURNS time AS $$
SELECT time '00:00:00' + $1 * interval '1 hour' + $2 * interval '1 min'
+ $3 * interval '1 sec'
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION mid(str text, pos int, len int)
RETURNS text AS $$
SELECT substring($1 FROM $2 FOR $3)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION right(str text, len int)
RETURNS text AS $$
SELECT substring($1 FROM length($1) - $2 FOR $2)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION strcmp(text, text)
RETURNS int AS $$
SELECT CASE WHEN $1 < $2 THEN -1
WHEN $1 > $2 THEN 1
ELSE 0 END;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION unix_timestamp()
RETURNS double precision AS $$
SELECT EXTRACT(epoch FROM current_timestamp)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION unix_timestamp(timestamp)
RETURNS double precision AS $$
SELECT EXTRACT(epoch FROM $1)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION utc_date()
RETURNS date AS $$
SELECT CAST(NOW() at time zone 'utc' AS date)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION utc_time()
RETURNS time AS $$
select timezone('UTC', now())
$$ LANGUAGE sql;

Click here for more articles about MySQL, PostgreSQL and other popular database management systems.