MySQL → PostgreSQL: A Worked Example

MySQL → PostgreSQL looks easy on the surface — both are open-source relational databases, the SQL rhymes, the types mostly line up. That surface is exactly what makes it dangerous. The syntax differences are obvious and mechanical; the behavioral differences are quiet and they’re where the data drifts. Swordfish carries 183 MySQL rules, and the ones that matter most are the behavioral ones.

Reproduce these against the sample MySQL app baked into the Docker image: cd ops && docker compose up -d, create a project with source type mysql, point it at /srv/test-apps, scan. The full assess → review → hand-off → diff workflow is in the Oracle post; this post focuses on the MySQL-specific findings.

The mechanical syntax conversions

These are low-risk and mostly confirm-and-move-on:

AUTO_INCREMENT (MYS-001, MYS-S006, MYS-B013) becomes an identity column:

-- MySQL:  id INT AUTO_INCREMENT PRIMARY KEY
-- Postgres:
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

Backtick identifiers (MYS-005) become double quotes — or, better, drop the quoting entirely and let the identifiers fold to lowercase:

-- MySQL:    SELECT `order`.`status` FROM `order`
-- Postgres: SELECT "order".status FROM "order"

IFNULL (MYS-003) and MySQL’s ISNULL (MYS-050) become COALESCE; the comma LIMIT (MYS-007) becomes LIMIT/OFFSET; GROUP_CONCAT (MYS-002) becomes string_agg:

-- MySQL:    SELECT GROUP_CONCAT(name) ... LIMIT 10, 20
-- Postgres: SELECT string_agg(name, ',') ... LIMIT 20 OFFSET 10

ENUM columns (MYS-S007) become either a native CREATE TYPE ... AS ENUM or a CHECK constraint. Swordfish recommends the check constraint by default because it’s easier to evolve.

The behavioral traps (read these twice)

Zero dates: MYS-BEH-001 / MYS-B003 (critical). MySQL happily stores '0000-00-00'. PostgreSQL rejects it outright — it’s not a valid date. Any column that used the zero date as a sentinel for “unknown” has to be migrated to NULL (or a real date), and every query that compares against '0000-00-00' has to change. This one fails at load time if you’re lucky and silently mis-filters if you’re not.

GROUP BY with non-aggregated columns: MYS-BEH-003 / MYS-B002 (high). MySQL’s default mode lets you SELECT a, b, c ... GROUP BY a and invents values for b and c. PostgreSQL requires every selected column to be aggregated or grouped. The code that relied on MySQL “just picking one” now won’t run, and when you fix it, you have to decide which value it should have picked, which is a business question the original code never answered.

Implicit type coercion: MYS-BEH-002 / MYS-B007 (high). MySQL compares '5' = 5 as true and silently coerces strings to numbers in predicates. PostgreSQL is strict. A WHERE varchar_col = 5 that worked in MySQL throws a type error in Postgres — better than the alternative, which is the comparisons that don’t error but now match different rows.

IFNULL vs COALESCE type behavior: MYS-BEH-005 (medium). Mechanically IFNULL(a, b) → COALESCE(a, b), but the two can resolve result types differently. Confirm the column type of the result didn’t change in a way that affects downstream math or formatting.

Case-insensitive comparison (review). MySQL’s default collation compares strings case-insensitively; PostgreSQL is case-sensitive. The login lookup, the “email already registered” check, the dedup job — all of them change behavior. Swordfish flags the collation-dependent patterns for review; the fix (LOWER() comparisons, CITEXT, or a case-insensitive collation) is a deliberate choice, not a find-and-replace.

The dump path

If you’re migrating from a mysqldump file rather than a live connection, Swordfish’s dump transformer rewrites the MySQL-specific DDL (engine clauses, AUTO_INCREMENT, backticks, ENUM, charset declarations) into PostgreSQL-compatible SQL as a starting point. It’s a starting point — you still review it, especially anywhere a zero date or an ENUM was involved.

The takeaway for MySQL specifically: budget your review time on the behavioral findings, not the syntax ones. The syntax is a afternoon. The zero dates, the loose typing, and the case-insensitivity are where a “done” migration quietly starts returning wrong answers. Next in the series: SQL Server.

Reproduce this walkthrough against the bundled sample apps: github.com/EnterpriseDB/swordfish-sample-apps. Swordfish itself (Apache-2.0): github.com/EnterpriseDB/swordfish-migrations