MySQL and PostgreSQL have very 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 information about the correct types mapping.
This is how basic MySQL types are converted into equivalent PostgreSQL types:
| 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 | TEXT |
| 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 a 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 |
Late versions of MySQL support spatial geometry types that also have equivalents in PostgreSQL and must be converted accordingly:
| MySQL | PostgreSQL |
| LINESTRING | LINE |
| POINT | POINT |
| POLYGON | POLYGON |
In comparison to PostgreSQL, all MySQL integer types (tinyint, smallint, int, bigint) can have UNSIGNED attribute. Unsigned specification forces to take positive numbers only with a 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 are to remember that in contrast to PostgreSQL, MySQL permits to store ‘0000-00-00’ into date columns. PostgreSQL specialists recommend interchanging such values by NULLs while migrating data from MySQL. However, if this approach somehow breaks the database logics, you must consider using another mapping for such date columns.
To simplify types mapping task in large migration projects, database specialists may use dedicated software tools automating such procedures. One among these tools is MySQL to PostgreSQL converter provided by Intelligent Converters. The product combines simple to use wizard-style interface with robust capabilities to convert huge and complicated databases:
- Converts MySQL DDL (tables, indexes and foreign keys) to PostgreSQL format handling all types mapping properly
- Supports command line in order to script, automate and schedule the database migration
- Reads MySQL database directly without middleware components like .NET, ADO, etc
- Uses PostgreSQL native API lib PQ to get the best performance of writing data
- Option to export into SQL script file to get more control over the conversion process (database specialist can review all DDLs and data before importing it to PostgreSQL server)
- All versions and variations of MySQL are supported as well as MariaDB and Percona
As every Intelligent Converters migration tool, MySQL to PostgreSQL converter comes with unlimited support and 1-year subscription for updates, that enables receiving new versions of the product inside one year after buying free.
