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.
MySQL pros:
- 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
MySQL cons:
- 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.
PostgreSQL pros:
- 100{0869658f5eb2b3e6517d509ddd28845c49986e33639eac98a83b2734215e1b2c} 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
PostgreSQL cons:
- 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
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’s important to have in mind primary differences between these DBMS.
Types
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):
| MySQL | PostgreSQL |
| BINARY(n) | BYTEA |
| BIT | BOOLEAN |
| 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) |
| DOUBLE | DOUBLE PRECISION |
| FLOAT | REAL |
| MEDIUMINT | INTEGER |
| TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB | BYTEA |
| TINYINT | SMALLINT |
| TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT | TEXT |
| VARBINARY(n), VARBINARY(max) | BYTEA |
| VARCHAR(max) | 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:
| MySQL | PostgreSQL |
| BIGINT AUTO_INCREMENT | BIGSERIAL |
| INTEGER AUTO_INCREMENT | SERIAL |
| SMALLINT AUTO_INCREMENT | SMALLSERIAL |
| TINYINT AUTO_INCREMENT | SMALLSERIAL |
Built-in Functions
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:
| MySQL | PostgreSQL |
| curtime() | current_time |
| DAY($a) or DAYOFMONTH($a) | extract(day from date($a))::integer |
| DATEDIFF($1, $2) | $1 – $2 |
| HOUR($1) | EXTRACT(hour FROM $1)::int |
| IFNULL($a,$b) | COALESCE($a,$b) |
| 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 |
| SYSDATE() | CURRENT_DATE |
| 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
