MySQL vs PostgreSQL

PostgreSQL vs MySQL | FOSS Linux

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% 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

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. 

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):

MySQLPostgreSQL
BINARY(n)BYTEA
BITBOOLEAN
CHAR(n), CHARACTER(n)CHAR(n), CHARACTER(n)
DATETIMETIMESTAMP [WITHOUT TIME ZONE]
DECIMAL(p,s), DEC(p,s)DECIMAL(p,s), DEC(p,s)
DOUBLEDOUBLE PRECISION
FLOATREAL
MEDIUMINTINTEGER
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOBBYTEA
TINYINTSMALLINT
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXTTEXT
VARBINARY(n), VARBINARY(max)BYTEA
VARCHAR(max)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:

MySQLPostgreSQL
BIGINT AUTO_INCREMENTBIGSERIAL
INTEGER AUTO_INCREMENTSERIAL
SMALLINT AUTO_INCREMENTSMALLSERIAL
TINYINT AUTO_INCREMENTSMALLSERIAL

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:

MySQLPostgreSQL
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 ENDLearn more about MySQL to PostgreSQL database migration at https://www.convert-in.com/mysql-to-postgres.htm

Leave a Reply

Your email address will not be published. Required fields are marked *