Source: Safe coversion of MySQL data types to PostgreSQL
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.