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