MySQL vs PostgreSQL data types: what changes in a migration
Most of a MySQL to PostgreSQL migration is boring and mechanical. The types below are the part that isn't: each one either doesn't exist on the other side, or exists with different behavior. Get these right and the rest is search and replace.
The mapping table
| MySQL | PostgreSQL | Watch for |
|---|---|---|
BIGINT AUTO_INCREMENT | BIGINT GENERATED BY DEFAULT AS IDENTITY | identity is a column property, not a type |
TINYINT(1) | BOOLEAN | convert DEFAULT 1 to DEFAULT TRUE with it |
TINYINT / MEDIUMINT | SMALLINT / INT | no 1- or 3-byte ints in Postgres |
INT UNSIGNED, BIGINT UNSIGNED | BIGINT, NUMERIC(20) | no unsigned: go up one size to keep the range |
DATETIME | TIMESTAMP | both zoneless; consider timestamptz while you're moving anyway |
TIMESTAMP | TIMESTAMPTZ | MySQL's TIMESTAMP converts through the session zone, like timestamptz |
ENUM('a','b') | VARCHAR + CHECK (or CREATE TYPE) | inline ENUM in a column is MySQL-only syntax |
TINYTEXT/MEDIUMTEXT/LONGTEXT | TEXT | one TEXT type, no size tiers |
BLOB family | BYTEA | |
DOUBLE | DOUBLE PRECISION | money should be NUMERIC on both sides |
The ones that bite
Unsigned integers. Postgres doesn't have them, and the
naive mapping (INT UNSIGNED -> INT) silently
halves the range: an id column already past 2.1 billion won't fit. Go up one
size (INT UNSIGNED -> BIGINT); the storage
difference is noise next to the overflow incident.
TINYINT(1). By convention it's MySQL's boolean, and
Postgres has a real BOOLEAN, so
convert the type and its defaults together. The trap is the column that used
the boolean type but not the boolean convention, a TINYINT(1)
holding 0/1/2, which is a status in disguise and needs a decision, not a
mapping.
The two temporal types swap roles. MySQL's
DATETIME is zoneless (like Postgres timestamp);
MySQL's TIMESTAMP converts through the session time zone (like
Postgres timestamptz) and additionally tops out in 2038 in older
versions. Map each to its behavioral twin, and if the columns are audit-style
(created_at), a migration is
the right moment to move
them to timestamptz regardless of the source type.
Inline ENUM and index syntax. status ENUM('draft',
'live') and KEY idx_status (status) inside CREATE TABLE
are MySQL dialect. Postgres wants a VARCHAR with a
CHECK (or a standalone enum type) and a separate CREATE
INDEX statement. Same information, different homes.
Behavior that changes without a type changing
Two non-type differences catch people in week one. Identifier case:
unquoted names fold to lowercase in Postgres, and quoted mixed-case names
("createdAt") must be quoted forever, migrate to snake_case and
never quote. Strictness: Postgres rejects out-of-range and zero dates
('0000-00-00' is not a date), where MySQL's historical modes
truncated and warned; data that "worked" in MySQL may refuse to load, which is
the migration telling you it was never valid.
Doing it mechanically
All of the table above is deterministic, which means it should be a tool, not a checklist someone follows by hand at 11pm. Schemint's MySQL to PostgreSQL converter applies exactly these rules to your DDL: identity from AUTO_INCREMENT, unsigned upsized, TINYINT(1) to BOOLEAN with defaults, ENUM to VARCHAR + CHECK, inline KEYs extracted to CREATE INDEX, ENGINE/CHARSET noise dropped. Paste the dump, read the diff, keep the judgment calls for yourself.