MySQL to PostgreSQL Migration Guide

Detect MySQL dialect quirks before data transfer starts. Map unsigned integers, zero dates, booleans, and triggers cleanly into PostgreSQL 16.

MySQL to PostgreSQL type mapping matrix

MySQL Source TypePostgreSQL Target TypeAnalyzer RuleRisk / Verification
INT UNSIGNEDBIGINTACTION REQUIREDPrevents 32-bit signed overflow for IDs above 2.14 billion
BIGINT UNSIGNEDNUMERIC(20, 0)ACTION REQUIREDPreserves full 64-bit unsigned range up to 1.84e19
TINYINT(1)BOOLEANACTION REQUIREDMaps 0/1 flags to false/true; switch to SMALLINT if values exceed 1
DATETIMETIMESTAMPTZACTION REQUIREDConfirm source session timezone and zero-date replacement policy
DECIMAL(20, 8)NUMERIC(20, 8)ACTION REQUIREDTransferred as exact decimal strings without IEEE-754 float rounding
JSONJSONBACTION REQUIREDDeduplicates object keys and supports GIN indexes
ENUM('a','b')TEXT CHECK (...)ACTION REQUIREDEnforces allowed values without global enum type coupling
0000-00-00 00:00:00NULL or sentinelACTION REQUIREDPostgreSQL rejects zero dates; replacement is explicit in plan
AUTO_INCREMENTGENERATED BY DEFAULT AS IDENTITYACTION REQUIREDResets target sequence to MAX(id) in Stage 7

Using pgloader alongside Database Migration Studio

Every MySQL-to-PostgreSQL plan also generates an inspectable pgloader command file if you prefer delegating high-throughput COPY streams on Linux or macOS while keeping Database Migration Studio for pre-flight analysis and SHA-256 post-migration verification.

dbmigrate analyze --config migration.yaml
dbmigrate plan --config migration.yaml --output plan.json
dbmigrate run --plan plan.json --job-id mysql-pg-prod
dbmigrate verify --job mysql-pg-prod

Frequently asked questions

Why does PostgreSQL fail on MySQL zero dates like 0000-00-00?

PostgreSQL enforces strict calendar validation on DATE and TIMESTAMP columns. Database Migration Studio detects zero-date defaults and lets you map them to NULL or a chosen valid date.

Are MySQL triggers such as trigger_update_balance copied automatically?

No. Procedural trigger syntax differs between MySQL and PL/pgSQL. Triggers, views, and stored procedures are listed under Unmigrated Objects before transfer begins.