MySQL to PostgreSQL: The Migration Checklist That Actually Matters

MySQL → PostgreSQL is the most travelled migration path in the industry, and it's still full of traps because the two engines disagree about fundamentals. Here's the checklist we encode into DBShifts's conversion rules — useful even if you're migrating by hand.

1. TINYINT(1) is a boolean. TINYINT isn't.

MySQL has no real boolean — BOOLEAN is an alias for TINYINT(1). A correct converter maps tinyint(1) → BOOLEAN but leaves plain TINYINT (a rating, a count) as SMALLINT. Convert both blindly and your rating column becomes true/false.

2. UNSIGNED widens, or it overflows

PostgreSQL has no unsigned integers. INT UNSIGNED holds values up to 4.2 billion — beyond signed INT's 2.1 billion max — so it must widen to BIGINT. And BIGINT UNSIGNED can exceed signed BIGINT entirely; values past 9.2 quintillion need NUMERIC(20).

3. ENUM and SET need a strategy

• ENUM('red','green','blue') → a native PostgreSQL CREATE TYPE … AS ENUM, preserving validation.
• SET('a','b','c') has no PG equivalent — TEXT[] keeps the multi-value semantics, and the comma-joined string must be split during transfer.

4. Zero dates will be rejected

MySQL happily stores 0000-00-00. PostgreSQL refuses it. Decide up front: NULL them (our default) or map to a sentinel. Either way, do it deterministically and report the count — silently failing rows are how migrations lose data.

5. AUTO_INCREMENT → sequences that don't collide

Mapping AUTO_INCREMENT to SERIAL is the easy half. The half everyone forgets: after bulk-inserting rows with explicit IDs, the backing sequence still points at 1. setval() every sequence to MAX(id) or your first production INSERT throws a duplicate-key error.

6. TIME is not what your driver thinks

MySQL drivers return TIME columns as durations (it's a -838h..838h range type), while PostgreSQL expects a time-of-day. Values inside 0–24h convert cleanly; out-of-range values need an explicit decision.

7. Defer constraints, then verify

Load data with FK constraints deferred and non-PK indexes dropped — it's dramatically faster and immune to table-ordering problems. Then re-apply, rebuild, and validate: per-table row counts, order-independent checksums on both sides, FK integrity on the target.