MySQL to PostgreSQL Types Mapping

[MySQL to PostgreSQL Converter]  [About Migration]  [How to Control Migration Results

MySQL and PostgreSQL have similar data types. Some of them are equivalent while others are not. When planning MySQL to PostgreSQL migration it is important to remember the following table of the correct types mapping:

MySQL PostgreSQL
BIGINT BIGINT
BINARY(n) BYTEA
BIT BOOLEAN
CHAR(n), CHARACTER(n) CHAR(n), CHARACTER(n)
DATE DATE
DATETIME TIMESTAMP [WITHOUT TIME ZONE]
DECIMAL(p,s), DEC(p,s) DECIMAL(p,s), DEC(p,s)
DOUBLE DOUBLE PRECISION
FLOAT REAL
INT, INTEGER INT, INTEGER
MEDIUMINT INTEGER
NUMERIC(p,s) NUMERIC(p,s)
SMALLINT SMALLINT
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB BYTEA
TINYINT SMALLINT
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT TEXT
TIME TIME [WITHOUT TIME ZONE]
TIMESTAMP TIMESTAMP [WITHOUT TIME ZONE]
VARBINARY(n), VARBINARY(max) BYTEA
VARCHAR(n) VARCHAR(n)
VARCHAR(max) TEXT

MySQL has an argument for integer-like columns called 'auto_increment', which increases the value of the field automatically each time when new row is inserted. PostgreSQL uses SERIAL type and its modifications for the same purpose:

MySQL PostgreSQL
BIGINT AUTO_INCREMENT BIGSERIAL
INTEGER AUTO_INCREMENT SERIAL
SMALLINT AUTO_INCREMENT SMALLSERIAL
TINYINT AUTO_INCREMENT SMALLSERIAL

Unlike PostgreSQL, all MySQL integer types (tinyint, smallint, int, bigint) can have UNSIGNED attribute. Unsigned specification forces to take positive numbers only with larger upper range of acceptable values. Here is how MySQL unsigned types have to be mapped into PostgreSQL:

MySQL PostgreSQL
BIGINT UNSIGNED NUMERIC(20)
INT UNSIGNED BIGINT
MEDIUMINT UNSIGNED INTEGER
SMALLINT UNSIGNED INTEGER
TINYINT UNSIGNED INTEGER

Another challenge while mapping types is to remember that unlike PostgreSQL, MySQL allows to store '0000-00-00' into date columns. PostgreSQL specialists suggest to replace such values by NULLs while migrating data from MySQL. However, if this approach somehow breaks the database logics, you should consider using another mapping for such date columns.