converting-from-mysql-to-postgresql-1-638

MySQL to PostgreSQL Types Mapping

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.

Image result for MySQL to PostgreSQL

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.

Image result for MySQL to PostgreSQL

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.

Post Author: Heather Moors

Heather@goodtechsystems.com'

Leave a Reply

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


*