Migrating schemas between MySQL and PostgreSQL sounds easy — until you actually try it.

At DBConvert Streams, we built a fully bidirectional DDL conversion engine that handles both MySQL → PostgreSQL and PostgreSQL → MySQL migrations. Along the way, we’ve hit nearly every ugly corner case you can imagine — and a few you probably can’t.

This is your ultimate cheat sheet — based on real production migrations, not just theoretical type mapping.

✅ What We Handle Automatically (Both Directions)

• Auto-increment vs sequences vs identity columns
• Primary keys, foreign keys, and constraints
• Universal foreign key handling across environments
• Indexes (unique, non-unique, partial)
• Bidirectional comment conversion
• Collation removal & charset normalization
• Zero date handling
• Schema vs database remapping
• Identifier quoting, reserved words, and length validation
• Default value normalization and timestamp handling

🔄 MySQL → PostgreSQL: Full Type Mapping

🔄 PostgreSQL → MySQL: Full Type Mapping

Note: PostgreSQL advanced types like ARRAYs and JSONB indexing are simplified or downgraded to ensure compatibility with MySQL.
Important: Automatic timestamp updates (ON UPDATE CURRENT_TIMESTAMP) in MySQL are not supported in PostgreSQL without triggers.

🛠 Preserving Constraints and Indexes

• Primary & Unique Keys: Recreated in the target database.
• CHECK Constraints: Supported and translated intelligently (e.g., MySQL CHECK(json_valid(data)) → PostgreSQL CHECK(jsonb_typeof(data) IS NOT NULL)).
• Indexes: Automatically recreated, ensuring consistent performance.

📝 Fully Bidirectional Comment Conversion

We preserve schema comments completely, both directions.

MySQL → PostgreSQL:

CREATE TABLE `users` (
  `id` BIGINT AUTO_INCREMENT COMMENT 'Primary key',
  `name` VARCHAR(255) COMMENT 'User\'s full name'
);

-- PostgreSQL output
CREATE TABLE "public"."users" (
  "id" BIGSERIAL NOT NULL,
  "name" CHARACTER VARYING(255)
);
COMMENT ON COLUMN "public"."users"."id" IS 'Primary key';
COMMENT ON COLUMN "public"."users"."name" IS 'User''s full name';

PostgreSQL → MySQL:

CREATE TABLE `users` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `name` VARCHAR(255) COMMENT 'User\'s full name'
) ENGINE=InnoDB;

✅ All table and column comments are preserved.

🔧 Universal Foreign Key Constraint Handling

Foreign key issues are one of the most frequent causes of migration failures. We’ve solved them with a unified, environment-aware strategy for both PostgreSQL and MySQL.

PostgreSQL (All environments):

• Uses a deferred constraint strategy:
  • Creates foreign keys after all tables and data are loaded.
  • Uses NOT VALID constraints for best performance.
  • Avoids the need for session_replication_role or superuser access.
  • Thread-safe, atomic, and only runs once per run.
• Eliminates failures like:
  • ERROR: relation "city" does not exist (SQLSTATE 42P01)

MySQL:

• Standard MySQL (AWS, GCP, Azure, DO, local):
  • Uses FOREIGN_KEY_CHECKS = 0 during data load.
  • Creates all foreign keys immediately after.
• PlanetScale/Vitess (MySQL-compatible, but limited):
  • Detected automatically via connection string.
  • Foreign keys deferred until after table creation.
  • Applies constraints only if supported.

Example Logs:

✅ Creating deferred foreign key constraints for PostgreSQL
✅ Found 5 tables with foreign key constraints to create
✅ Foreign key creation completed: 15/15 constraints created successfully

✅ Detected MySQL cloud provider (PlanetScale) with foreign key limitations
✅ Creating deferred foreign key constraints for MySQL cloud provider: PlanetScale

⚠️ Edge Cases and Advanced Handling

DBConvert Streams covers nuanced cases effectively:

• Identifier Length Validation: Ensures name length limits (PostgreSQL: 63 chars, MySQL: 64 chars).
• Binary Data Types: Intelligent conversion of VARBINARY to BYTEA/LONGBLOB.
• JSON: Automatic JSON→JSONB conversion.
• ENUM/SET: Safe fallback to TEXT.
• Zero Dates: Converts invalid zero dates ('0000-00-00') to NULL.
• YEAR Type: Uses SMALLINT for year storage.

🎯 Schema Migration Philosophy

🧪 Comprehensive Testing

• 70+ schema migration scenarios tested
• Identifier length rules (PostgreSQL 63, MySQL 64)
• Binary type size limits
• Comments, default values, and quoting
• Constraint validation logic
• Foreign key creation in real-world dependency chains

🚦 DBConvert Streams vs. Other Tools

* Each migration direction is handled as a separate task/session (not true sync in a single job).

🚧 Out-of-Scope Features

Currently not supported:

• Stored procedures and triggers.
• Computed/generated columns.
• Partitioning.
• Composite/user-defined types.
• Full-text search indices.
• PostGIS (spatial extensions).

🚀 Conclusion & Next Steps

With DBConvert Streams, you get:

• Accurate, safe, and full-featured schema conversion.
• Full comment, key, and index preservation.
• Universal FK handling—no hacks, no superuser, no manual fiddling.
• Real-time migration or one-shot moves, your choice.

Skip the pain of manual DDL fixes and broken migrations.
👉 Try DBConvert Streams today