When considering database migration from MySQL to PostgreSQL or backward, database specialists should understand primary pros and cons of each DBMS before they begin. This whitepaper explores strong and weak sides of MySQL and PostgreSQL as well as differences in features or capabilities that may become bottleneck of the migration process.
- Easy to learn and use
- Each table can have a different storage engine.
- Each storage engine has particular behavior, features, and properties.
- Tight integration into the web
- Does not support user-defined types.
- Does not support recursive queries.
- Does not support roll-back transactions for DDL statements such as “ALTER TABLE” or “CREATE TABLE”
- Does not support materialized views.
- Does not support sequences, although it can be emulated.
- 100% implementation of SQL standard
- Support for advanced data types, such as multi-dimensional arrays, user-defined types, etc
- Sophisticated locking mechanism
- Point-in-time recovery
- It is quite complicated to learn and use
- It is less popular than MySQL that means it is a little harder to get community support or find all required answers in knowledge base
After comparing those two database management systems, let us ask a question: is this a right choice to migrate 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’s important 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|
PostgreSQL does not have equivalent of MySQL ‘auto_increment’ feature, which allows automatically increase the field’s value when new row is inserted. Instead, PostgreSQL provides 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 ENDLearn more about MySQL to PostgreSQL database migration at https://www.convert-in.com/mysql-to-postgres.htm