When considering database migrationfromMySQLto PostgreSQL orbackward, database specialists should understandprimary pros and consof each DBMS before they begin. This whitepaper explores strong and weak sides of MySQLand PostgreSQL as well as differences in features or capabilities that may become bottleneck of the migration process.
Now it is possible to answer the question: is it good to switch from MySQL to PostgreSQL. It is reasonable for a large data warehouse that must be scaled or deployed into more complicated system. At the same time, it does not make sense for small and medium databases with a simple semantics.
When planning a database migration from MySQL to PostgreSQL, it’simportant to have in mind primary differences between these DBMS.
MySQL and PostgreSQL have different sets of data types, however there is straightforward mapping between them according the table below (there are only distinguished types listed):
|CHAR(n), CHARACTER(n)||CHAR(n), CHARACTER(n)|
|DATETIME||TIMESTAMP [WITHOUT TIME ZONE]|
|DECIMAL(p,s), DEC(p,s)||DECIMAL(p,s), DEC(p,s)|
|TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB||BYTEA|
|TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT||TEXT|
Unlike MySQL, PostgreSQL does not have property ‘auto_increment’ for integer columns, which increases the value of the field automatically each time when new row is inserted. Instead, it uses SERIAL type and its variations for the same purpose:
Those functions are used in views and stored procedures, each of them must be converted into PostgreSQL equivalent before passing it to the destination DBMS. Here is conversion of the most popular MySQL built-in functions into PostgreSQL:
|DAY($a) or DAYOFMONTH($a)||extract(day from date($a))::integer|
|DATEDIFF($1, $2)||$1 – $2|
|HOUR($1)||EXTRACT(hour FROM $1)::int|
|INSTR($a, $b)||position($b in $a)|
|ISNULL($a)||$a IS NULL|
|LOCATE ($a,$b)||INSTR($a, $b)|
|minute($1)||EXTRACT(minute FROM $1)::int|
|month($1)||EXTRACT(month FROM $1)::int|
|WEEK($1)||extract(week from ($1))::int|
|YEAR($1)||extract(year from $1)|
MySQL pattern IF($a,$b,$c) can be converted into PostgreSQL equivalent: CASE WHEN $a THEN $b ELSE $c END
Learn more about MySQL to PostgreSQL database migration at https://www.convert-in.com/docs/sql2pgs/intro.htm