Migrate MySQL to PostgreSQL

MySQL vs PostgreSQL

MySQL

the world's most deployed open-source relational database.

PostgreSQL

the most advanced open-source relational database.

How the migration works

MySQL to PostgreSQL data type mapping

Pulled directly from DBShifts's own conversion rules — not a general reference table, this is what actually runs.

Example: MySQL to PostgreSQL schema conversion

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  is_active TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

MySQL → PostgreSQL conversion notes

Engine-specific rules baked into the conversion and transfer pipeline — verified by live certification of this exact pair.

• TINYINT(1) is treated as a boolean; plain TINYINT stays numeric — converting both blindly corrupts ratings and counters.
• UNSIGNED integers are widened on targets without unsigned types, so values above the signed range survive.
• ENUM and SET columns are converted with their value lists preserved (native enums, CHECK constraints, or arrays depending on the target).
• TIME columns are durations (-838h..838h) at the driver level — DBShifts converts them per target instead of failing the batch.
• Zero dates (0000-00-00) are normalized deterministically and reported, never silently dropped.
• Bulk loads use sequence reseeding (setval to MAX) so SERIAL columns keep working after migration.
• ENUMs from MySQL become native CREATE TYPE … AS ENUM — validation semantics preserved.
• Foreign keys and indexes are applied after data load for dramatically faster transfers.

Frequently asked questions

Go deeper

Related migration paths