Convert MySQL to PostgreSQL without losing foreign keys

A cross-engine conversion is not a transfer. Two databases that hold the same rows can still disagree about what those rows are, and every published horror story about MySQL → PostgreSQL is really a story about a type that was mapped carelessly.

This page is the mapping, written down, plus the four places where a conversion needs a decision from you rather than from us. Rate is $4/GB; pre-flight prices and checks the pair before anything is created or charged.

How foreign keys survive

They survive because data is loaded with constraints off and every reference is validated afterwards, not because we hope.

The schema is created first — tables, columns, sequences — with no foreign keys.
Data loads table by table, in parallel, in bounded batches.
Indexes and foreign keys are applied afterwards, in dependency order.
Sequences are reset to match the data that was loaded.
Row counts per table and total foreign-key counts are compared against the source.

Step 5 is the one that matters. A conversion that cannot prove it matches the source fails closed — it does not report success on a partial result, and an orphaned row that would violate a foreign key stops the migration rather than being quietly dropped. If you have dangling references in MySQL today, you will find out here. That is usually the first time anyone has checked.

MySQL lets those accumulate: a table created as MyISAM, or with FOREIGN_KEY_CHECKS=0 set during some past import, will happily hold rows pointing at parents that no longer exist. Find them before you migrate:

SELECT c.* FROM child c
LEFT JOIN parent p ON p.id = c.parent_id
WHERE c.parent_id IS NOT NULL AND p.id IS NULL;

Delete them, or repoint them, and the conversion goes through.

The type mapping

MySQL	PostgreSQL	Note
`TINYINT(1)`	`BOOLEAN`	The Laravel/Rails boolean convention
`TINYINT` (other widths)	`SMALLINT`	Keeps the numeric range
`TINYINT UNSIGNED`	`SMALLINT`
`SMALLINT UNSIGNED`	`INTEGER`	Widened to hold the unsigned range
`INT` / `INTEGER`	`INTEGER`
`INT UNSIGNED`	`BIGINT`	Widened
`MEDIUMINT`	`INTEGER`
`BIGINT` / `BIGINT UNSIGNED`	`BIGINT`	See below
`DECIMAL(p,s)` / `NUMERIC(p,s)`	unchanged	Precision preserved exactly
`FLOAT`	`REAL`
`DOUBLE`	`DOUBLE PRECISION`
`BIT(1)`	`BOOLEAN`
`ENUM(...)`	`VARCHAR(255)`	See below
`SET(...)`	`TEXT`
`JSON`	`JSON`
`BLOB` family	`BYTEA`	Values preserved byte-for-byte
`DATETIME` / `TIMESTAMP`	timestamp types	See zero dates, below

`BIGINT UNSIGNED` becomes `BIGINT`, deliberately

The technically pure mapping is NUMERIC, since Postgres has no unsigned integers and BIGINT UNSIGNED can hold values BIGINT cannot. We do not do that, and the reason is foreign keys.

An auto-increment primary key is emitted as BIGINT. If an unsignedBigInteger foreign key column — user_id, created_by, every FK any Laravel schema has ever written — became NUMERIC, then every foreign key referencing that primary key would fail to build: incompatible types: numeric and bigint. The purer mapping breaks referential integrity, which is the entire point of the exercise.

Real auto-increment ids never approach 2⁶³. BIGINT is both safe and referentially compatible. If you genuinely store unsigned values above 9.2 quintillion in a non-key column, that column needs handling by hand — but you would know.

`ENUM` becomes `VARCHAR(255)`, and you lose the constraint

The values are preserved. The restriction is not: after conversion, the column will accept any string.

Postgres has real enum types, but creating one changes what your application must do to add a value later (ALTER TYPE ... ADD VALUE, which has its own transactional rules), and silently converting a MySQL ENUM into a Postgres ENUM hands you a migration story you did not ask for. A VARCHAR behaves like the loosest reading of what you had.

If you want the constraint back, add it after the migration completes — a check constraint is the low-friction version:

ALTER TABLE orders
  ADD CONSTRAINT orders_status_check
  CHECK (status IN ('pending','paid','shipped','cancelled'));

Zero dates become `NULL` — and that can fail a `NOT NULL` column

MySQL accepts 0000-00-00 and 0000-00-00 00:00:00. PostgreSQL does not; there is no such date, and no mapping exists that preserves it. Those values are converted to NULL.

This is the one that bites. If a column is NOT NULL and contains zero dates — an entirely normal state of affairs in a MySQL schema of any age — the row cannot be inserted, and the conversion fails closed rather than inventing a date for you.

Find them before you start, for every date/datetime column that is NOT NULL:

SELECT COUNT(*) FROM your_table WHERE your_date_column = '0000-00-00 00:00:00';

Then decide what the value should actually be — the row's created_at, an epoch, or NULL with the column made nullable — and fix it in MySQL first. Only you know which is correct, which is exactly why we will not guess.

Auto-increment becomes a sequence, reset after load

MySQL's AUTO_INCREMENT becomes a Postgres sequence. Sequences are reset once, after all data is loaded, to the maximum id actually present — so the next insert on the target continues where the source left off rather than colliding with row 1.

If your application writes ids explicitly for some tables, check those sequences after cutover.

Identifier case

MySQL on Linux is case-sensitive about table names; MySQL on macOS usually is not. PostgreSQL folds unquoted identifiers to lower case. A table called Orders in MySQL becomes orders in Postgres, and a query that says SELECT * FROM "Orders" will not find it.

If your application quotes identifiers or uses mixed-case names, grep for that before cutover. Most ORMs are fine; hand-written SQL is where this shows up.

What has no equivalent

Triggers, stored procedures and events are MySQL-dialect code. They are not translated — PL/SQL-flavoured MySQL and PL/pgSQL are different languages, and a machine translation of business logic is not something you should accept sight-unseen. Port them by hand.
ON UPDATE CURRENT_TIMESTAMP has no direct Postgres equivalent; it is a trigger there. Add one if you rely on it.
Spatial types need PostGIS on the target.

Same data is not same performance

Verification proves every row and every foreign key arrived intact. It says nothing about how fast the new database answers your queries — that is a different property, and it changes in a cross-engine move. Postgres plans queries differently, locks differently, and pays a different price per round trip than MySQL did.

The failure that actually bites is rarely a slow query in isolation. It is a query your code runs in a loop — one lookup per item, hundreds of times per request — that the old database was simply fast enough to hide. A few extra milliseconds per call costs nothing once and minutes when it is multiplied, and nothing in your codebase says "this loop requires millisecond reads"; the assumption just lives in the design until the store under it changes.

So before the target takes traffic, run your application's hottest paths against it and compare timings — not benchmarks, your real endpoints. What you find is usually not a Postgres problem but an N+1 pattern that was always there, and this is the cheapest moment you will ever have to fix it.

Order of operations

Fix orphaned foreign-key rows in MySQL.
Fix zero dates in NOT NULL date columns.
Install any extensions the target needs.
Run pre-flight — it prices the conversion and checks both databases are reachable, creating and charging nothing.
Migrate, and let the verification step finish.
Add back any CHECK constraints you want in place of ENUMs.
Port triggers and procedures by hand.
Run your application's hottest paths against the target and compare timings — identical data does not mean identical query plans.
Back up the new database before it takes traffic.