When planning upgrade or transfer to new platform, every database specialist tries to find the best solution to store database according to particular requirements. One of the most popular need is to minimize total cost of ownership (TCO). This may lead to choosing one of open source database management systems. The most powerful and popular open source DBMS are MySQL and PostgreSQL. This whitepaper helps to understand basic pros and cons of these systems as well as differences in capabilities that could complicate the MySQL to PostgreSQL or backward migration process.
Benefitsof MySQL:
- It is easy to install and use compared to PostgreSQL
- Each table can have a different storage engine with its own features
- Excellent support in all modern programming languages
- Tight integration into the web
Disadvantages of MySQL:
- It does not support user-defined types
- It has no support for recursive queries
- It does not have materialized views
- It does not support sequences, although it can be emulated.
- It does not support roll-back transactions for DDL statements such as “ALTER TABLE” or “CREATE TABLE”
Benefits of PostgreSQL:
- It has 100% compliance with SQL standard
- It supports point-in-time recovery
- It has sophisticated locking mechanism
- It supportsadvanced data types such as multi-dimensional arrays, spatial, etc
Disadvantages of PostgreSQL:
- It is essentially slower than MySQL
- It is quite complicated to manage and develop compared to MySQL
- It is not so popular as MySQL,so it may be hard to get community support
This information helps to decide if it is reasonable to switch from MySQL to PostgreSQL or vice versa. PostgreSQL is good choice for large and complex corporate databases with perspective of scaling or deploying. On another hand, it does not make sense to use PostgreSQL for small and medium databases with a simple semantics. MySQL looks like a better choice for this purpose.
Everyone who consider database migration from MySQL to PostgreSQL or backward should remember the primary differences between these DBMS.
Types
Although data typesof MySQL and PostgreSQL are not equal, there is straightforward conversion between them.The table below illustrates conversion for distinguished types from MySQL to PostgreSQL:
| 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 |
Another difference between two DBMS is that PostgreSQL does not have property similar to MySQL’auto_increment’ increasing the value of the field each time when new row is inserted. For simple auto_increment columns (with base and step equal to 1) PostgreSQL SERIAL typesmay be used:
| MySQL | PostgreSQL |
| BIGINT AUTO_INCREMENT | BIGSERIAL |
| INTEGER AUTO_INCREMENT | SERIAL |
| SMALLINT AUTO_INCREMENT | SMALLSERIAL |
| TINYINT AUTO_INCREMENT | SMALLSERIAL |
More complicated variations of MySQL auto_increment can be replaced by combination of sequence and trigger on insert.
Built-in Functions
Both MySQL and PostgreSQL have wide range of built-in functions to use in views and stored procedures.Each of these functions must be converted into the appropriate equivalent before passing it to the destination DBMS. Here is list of MySQL to PostgreSQL conversion for the most popular built-in functions:
| 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) |
Learn more about differentaspects of MySQL to PostgreSQL and backward migration at https://www.convert-in.com/mysql-to-postgres.htm.
