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.
Detect MySQL dialect quirks before data transfer starts. Map unsigned integers, zero dates, booleans, and triggers cleanly into PostgreSQL 16.
| MySQL Source Type | PostgreSQL Target Type | Analyzer Rule | Risk / Verification |
|---|---|---|---|
INT UNSIGNED | BIGINT | ACTION REQUIRED | Prevents 32-bit signed overflow for IDs above 2.14 billion |
BIGINT UNSIGNED | NUMERIC(20, 0) | ACTION REQUIRED | Preserves full 64-bit unsigned range up to 1.84e19 |
TINYINT(1) | BOOLEAN | ACTION REQUIRED | Maps 0/1 flags to false/true; switch to SMALLINT if values exceed 1 |
DATETIME | TIMESTAMPTZ | ACTION REQUIRED | Confirm source session timezone and zero-date replacement policy |
DECIMAL(20, 8) | NUMERIC(20, 8) | ACTION REQUIRED | Transferred as exact decimal strings without IEEE-754 float rounding |
JSON | JSONB | ACTION REQUIRED | Deduplicates object keys and supports GIN indexes |
ENUM('a','b') | TEXT CHECK (...) | ACTION REQUIRED | Enforces allowed values without global enum type coupling |
0000-00-00 00:00:00 | NULL or sentinel | ACTION REQUIRED | PostgreSQL rejects zero dates; replacement is explicit in plan |
AUTO_INCREMENT | GENERATED BY DEFAULT AS IDENTITY | ACTION REQUIRED | Resets target sequence to MAX(id) in Stage 7 |
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
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.
No. Procedural trigger syntax differs between MySQL and PL/pgSQL. Triggers, views, and stored procedures are listed under Unmigrated Objects before transfer begins.