From:       To:      
Home > Documentation > PostgreSQL to MySQL

PostgreSQL to MySQL Types Mapping

[Postgres to MySQL Converter]  [About Migration]  [FAQ

Although PostgreSQL and MySQL have similar data types, those sets are not absolutly equal. When migrating from PostgreSQL to MySQL, MariaDB or Percona it is necessary to map data types accordng to the table below:

PostgreSQL MySQL
ARRAY LONGTEXT
BIGINT BIGINT
BIGSERIAL BIGINT auto_increment
BOOLEAN BIT
BYTEA LONGBLOB
CHAR(n), CHARACTER(n) CHAR(n), CHARACTER(n)
CIDR VARCHAR(43)
DATE DATE
DECIMAL(p,s), DEC(p,s) DECIMAL(p,s), DEC(p,s)
DOUBLE PRECISION DOUBLE
FLOAT(p) FLOAT(p)
INET VARCHAR(43)
INT, INTEGER INT
INTERVAL TIME
JSON LONGTEXT
LINE LINESTRING
MONEY DECIMAL(19,2)
NUMERIC(p,s) DECIMAL(p,s)
REAL FLOAT
SERIAL INT IDENTITY
SMALLINT SMALLINT
TEXT LONGTEXT
TIME(p) TIME(p)
TIMESTAMP DATETIME
TSVECTOR LONGTEXT
TSQUERY LONGTEXT
UUID VARCHAR(36)
VARCHAR(n) VARCHAR(n)
XML LONGTEXT

Note, that PostgreSQL and MySQL handle spatial data types in quite different ways. PostgreSQL offers straightforward method of inserting spatial data. For example, this is how to insert new data into column "position" having POINT type:

INSERT INTO geoposition(..., "position", ...) VALUES (..., POINT(12.3456789, -98.7654321), ...)

And you will be able to see the data "as is" when running a SELECT-query over that table. On another hand, MySQL stored data of POINT type in a binary form that does not make sense for end-user. Therefore, it is required to use special function ST_asText to convert internal representation of the spatial data into readable form:

SELECT ..., ST_asText(`position`), ... FROM geoposition