MySQL vs PostgreSQL

June 18, 2018 Simon Hopes | Comments Off

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.

MySQLpros:

  • Easy to learn and use
  • Eachtablecan have adifferentstorageengine.
  • Eachstorageenginehasparticularbehavior, features, andproperties.
  • Tight integration into the web

MySQLcons:

  • Does notsupport 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 notsupport materialized views.
  • Does notsupportsequences, 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

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.

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

Learn more about MySQL to PostgreSQL database migration at https://www.convert-in.com/docs/sql2pgs/intro.htm