MySQL → PostgreSQL data type mapping
This is exactly how our converter maps each MySQL type when migrating to PostgreSQL. The same defaults are used by the battle-tested pgloader engine.
| MySQL type | PostgreSQL type | Notes |
|---|---|---|
| TINYINT(1) | boolean | MySQL's de-facto boolean becomes a real boolean |
| TINYINT / SMALLINT | smallint | |
| MEDIUMINT / INT | integer | UNSIGNED columns are widened to the next larger type |
| BIGINT | bigint | BIGINT UNSIGNED becomes numeric(20) |
| DECIMAL(p,s) | numeric(p,s) | exact precision preserved |
| FLOAT | real | |
| DOUBLE | double precision | |
| CHAR(n) / VARCHAR(n) | char(n) / varchar(n) | |
| TINYTEXT…LONGTEXT | text | PostgreSQL text has no length limit |
| BINARY / VARBINARY / BLOB | bytea | |
| DATE | date | zero dates (0000-00-00) become NULL |
| DATETIME / TIMESTAMP | timestamptz | zero timestamps become NULL |
| TIME | time | |
| YEAR | integer | |
| ENUM('a','b') | custom ENUM type | a real PostgreSQL enum type is created |
| SET | text[] | |
| JSON | json | |
| GEOMETRY / POINT… | point / geometry | depends on the spatial type |
| BIT(n) | bit(n) | |
| AUTO_INCREMENT | sequence + default | sequences are reset to the right value |
Ready to migrate? Convert your MySQL dump now — free up to 10 MB with a free account (e-mail sign-in, no password).