Published: 2026-06-14

Migrate MySQL to PostgreSQL

Jam SQL Studio migrates MySQL (and MariaDB) tables to PostgreSQL — schema and data — directly from the Object Explorer. Right-click a table or a whole database, point it at a PostgreSQL connection, and Jam generates a translated CREATE TABLE, copies the rows with value coercion, and verifies the row counts. The headline translations: tinyint(1) → boolean, datetime → timestamp, AUTO_INCREMENT → GENERATED AS IDENTITY, and backtick identifiers → double quotes.

The mechanics — connect both, right-click Migrate Table to Connection…, Preview, Migrate — are the same across every pair; see the cross-engine workflow for the full walkthrough. This page focuses on what's specific to MySQL → PostgreSQL.

MySQL → PostgreSQL type mapping

Every type flows through Jam's canonical model. Here's what each MySQL type becomes in PostgreSQL:

MySQLPostgreSQLNote
tinyint(1)booleanMySQL's idiomatic boolean; 0/1 → FALSE/TRUE
tinyintsmallintWidened: PostgreSQL has no 1-byte int
smallintsmallint
mediumintintegerWidened (no 3-byte int)
int / integerinteger
bigintbigint
… unsignedsigned equivalentGotcha: no unsigned in PostgreSQL — review columns using the high range
decimal(p,s) / numericnumeric(p,s)Exact
floatreal
doubledouble precision
char(n) / varchar(n)character(n) / varchar(n)
tinytext / text / mediumtext / longtexttextAll sizes collapse to text
tinyblob / blob / mediumblob / longblobbyteaBinary, hex-encoded on copy
binary / varbinarybytea
datetimetimestampTz-less
timestamptimestampCompared tz-less for fidelity
datedate
timetime
jsonjsonbJSON copied as a quoted JSON string
enum(...) / set(...)carried verbatim + warningSee below

MySQL-specific gotchas

  • tinyint(1) vs tinyint. Only the display width 1 signals a boolean. tinyint and tinyint(4) are real integers and become smallint. If you stored true booleans as a plain tinyint, they'll arrive as smallint 0/1 — change the column to boolean after migrating if you want PostgreSQL's type.
  • ENUM and SET. MySQL inlines the allowed values in the column type. PostgreSQL models these differently (a CREATE TYPE ... AS ENUM, or a CHECK constraint on text), so Jam carries the column verbatim with a warning rather than guessing. Decide the target shape — a native PostgreSQL enum type or a text column with a CHECK — and apply it after migrating.
  • unsigned. PostgreSQL has no unsigned integers. The value is carried into the signed type; if a column genuinely used the unsigned high range, widen it (e.g. int unsigned → bigint).
  • Zero dates. MySQL permits '0000-00-00', which PostgreSQL rejects. Clean these in the source (or expect a warning) before migrating.
  • Identifiers. MySQL backticks become PostgreSQL double quotes, and PostgreSQL folds unquoted names to lowercase — so MyTable becomes mytable unless it was quoted.

What stays manual

As with every cross-engine migration, table shape and data come across automatically but procedural code does not: views, stored procedures, functions, and triggers are written in MySQL's SQL/PSM dialect and don't translate 1:1 to PL/pgSQL. After migrating, run a cross-engine Schema Compare to get the by-name inventory of what's missing on PostgreSQL, then port each by hand. The PL/pgSQL reference covers the target dialect (much of it applies regardless of source), and Finish a PostgreSQL schema is the day-after checklist.

Frequently asked questions

How does tinyint(1) migrate from MySQL to PostgreSQL?

MySQL's tinyint(1) is its idiomatic boolean, so Jam SQL Studio maps it to PostgreSQL boolean and coerces the stored 0/1 values to FALSE/TRUE. A tinyint with any other display width (tinyint, tinyint(4)) is a real 1-byte integer and maps to smallint, because PostgreSQL has no 1-byte integer type.

What happens to AUTO_INCREMENT when migrating MySQL to PostgreSQL?

A MySQL AUTO_INCREMENT column becomes a PostgreSQL GENERATED AS IDENTITY column. After the rows are copied, Jam resets the backing sequence so the next inserted id continues past the highest migrated value — you don't run setval by hand.

How are MySQL unsigned integers handled in PostgreSQL?

PostgreSQL has no unsigned integer types. Jam carries the value faithfully into the signed equivalent, but a column whose unsigned range was actually used (for example INT UNSIGNED storing values above 2.1 billion) should be reviewed — widen it to bigint if the high range matters. The preview flags the column type so you can decide.