mysql
postgresql

Migrate MySQL to PostgreSQL

Certified — live-tested path

The converter maps 20 MySQL column types to PostgreSQL. 2 of them change kind on the way (YEAR becomes SMALLINT, SET('a','b') becomes TEXT[]); the table below lists every one. DBShifts converts the schema, copies the rows in parallel batches and checks the result with row counts and checksums.

Start MySQL → PostgreSQL Migration

Free tier · no credit card

Not ready to connect a database? Paste your CREATE TABLE statements and see the PostgreSQL schema now

MySQL vs PostgreSQL

mysql

MySQL

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

postgresql

PostgreSQL

the most advanced open-source relational database.

MySQL to PostgreSQL data type mapping

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

MySQLPostgreSQL
TINYINT(1)BOOLEAN
INT / INTEGERINTEGER
INT UNSIGNEDBIGINT
BIGINT UNSIGNEDNUMERIC(20)
DECIMAL(p,s)NUMERIC(p,s)
VARCHAR(n)VARCHAR(n)
TEXT / LONGTEXTTEXT
DATETIME(p)TIMESTAMP(p)
TIMESTAMPTIMESTAMPTZ
JSONJSONB
ENUMnative PostgreSQL ENUM
SETTEXT[]
BLOB / BINARYBYTEA
AUTO_INCREMENTidentity column

Example: MySQL to PostgreSQL schema conversion

MySQL

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
);

PostgreSQL

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
);

TINYINT(1) becomes BOOLEAN, and = 1 stops working

MySQL has no real boolean: BOOLEAN is an alias for TINYINT(1), and WHERE is_active = 1 is how most MySQL code tests one. PostgreSQL's BOOLEAN is a separate type, and comparing it with an integer is an error, not a coercion: operator does not exist: boolean = integer. The schema converts cleanly. The queries are what break.

With the target schema connected, the SQL rewriter changes = 1 and = 0 to TRUE and FALSE, but only on columns that are boolean on the PostgreSQL side. It does not guess from the literal, because turning every id = 1 into id = TRUE would be worse than no rewrite at all. The other trap is data: MySQL lets a TINYINT(1) column hold 2 or -1, and those rows are not booleans. Run SELECT DISTINCT on each such column first.

Division gives different answers

In MySQL, 5 / 2 is 2.5000 and 1 / 0 is NULL. In PostgreSQL, 5 / 2 between two integers is 2, and 1 / 0 raises division by zero and aborts the transaction. The same report query returns a different number, or fails, while the data underneath is identical.

The rewriter keeps MySQL's result rather than PostgreSQL's: the division is computed as NUMERIC and a zero divisor yields NULL, so a rewritten query answers exactly what it answered before. Hand-ported SQL is where this goes wrong, usually in averages and percentages, and usually unnoticed until someone compares two dashboards.

ON DUPLICATE KEY UPDATE and REPLACE INTO

MySQL upserts with INSERT … ON DUPLICATE KEY UPDATE or REPLACE INTO. PostgreSQL's form is INSERT … ON CONFLICT (columns) DO UPDATE, and it has to name the conflict target. With the schema known, the rewriter fills in the primary key, which is the key MySQL's version was implicitly using.

REPLACE was never quite an upsert. MySQL implements it as a delete followed by an insert, so it fires delete triggers and ON DELETE CASCADE on child rows, and a new AUTO_INCREMENT value appears where the old row was. ON CONFLICT DO UPDATE does none of that. That is usually what the code wanted, but check any table whose children were being silently cascaded away.

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

Is MySQL to PostgreSQL migration production-ready in DBShifts?

Yes. MySQL to PostgreSQL is a certified pair: it passes the live certification suite on real MySQL and PostgreSQL servers, which checks schema fidelity, row counts, type-aware checksums and paging across batches.

What happens to MySQL types that PostgreSQL doesn't have?

Each gets the nearest PostgreSQL type the converter treats as safe: YEAR becomes SMALLINT, SET('a','b') becomes TEXT[]. Every such decision is in the migration plan before anything runs, and any mapping can be overridden per column.

Can I keep MySQL and PostgreSQL in sync after the initial migration?

Yes, until you cut over. DBShifts reads the row-based binary log (binlog_format = ROW). With zero-downtime mode on, the log position is captured before the first row is copied, so writes made during the copy are replayed afterwards.

Which MySQL data types need a second look when moving to PostgreSQL?

The table above carries a note on 9 of them: TINYINT(1) (BOOLEAN), FLOAT (REAL), DOUBLE (DOUBLE PRECISION), TIMESTAMP (TIMESTAMPTZ) and more. Analysis flags the same columns on your own schema before any data moves.

Go deeper

Ready to move MySQL to PostgreSQL?

Set up in under two minutes. Validation and rollback included.

Start Free Migration