From:       To:      

Convert MySQL views into PostgreSQL format

[MySQL to PostgreSQL Converter]  [FAQ]  [Download]  [Buy]

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.

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.