intermediate 1-2 weeks for typical databases100% Free Guide

How to Migrate from PostgreSQL to MySQL

Migrating from PostgreSQL to MySQL typically means losing some PostgreSQL-exclusive features. This guide covers what changes are required, what features must be simplified, and how to minimize risk during the migration.

9

Data Type Mappings

4

Syntax Differences

5

Migration Steps

2

Free Tools

Data Type Mappings

PostgreSQLMySQLNotes
SERIAL / GENERATED AS IDENTITYINT AUTO_INCREMENT—
BOOLEANTINYINT(1)MySQL has no native BOOLEAN; TINYINT(1) is the convention
TIMESTAMPTZDATETIME (with application TZ handling)MySQL has no timezone-aware timestamp type
JSONBJSONMySQL JSON is not as fast or indexable as PostgreSQL JSONB
UUIDCHAR(36) or BINARY(16)MySQL has no native UUID type
ARRAY typesNo direct equivalent (use related table)MySQL does not support array column types
HSTOREJSON columnUse MySQL JSON column as key-value store
NUMERIC(p,s)DECIMAL(p,s)Functionally identical
TEXTTEXT or LONGTEXT (for >65KB)—

SQL Syntax Differences

Upsert

PostgreSQL

INSERT ... ON CONFLICT DO UPDATE

MySQL

INSERT ... ON DUPLICATE KEY UPDATE

EXCLUDED keyword does not exist in MySQL; use VALUES(col)

LATERAL JOIN

PostgreSQL

Supported: CROSS JOIN LATERAL (...)

MySQL

Not supported (use subquery or derived table workaround)

Significant rewrite required for complex LATERAL queries

Full Text Search

PostgreSQL

tsvector/tsquery with GIN indexes

MySQL

FULLTEXT INDEX with MATCH...AGAINST

Completely different FTS syntax and capabilities

Window Functions

PostgreSQL

Full support including FILTER clause

MySQL

Supported since MySQL 8.0 (limited FILTER support)

Ensure MySQL 8.0+ is used

Step-by-Step Migration Guide

1

Assess PostgreSQL-specific features in use

Identify: JSONB queries, ARRAY columns, LATERAL JOINs, recursive CTEs, partial indexes, custom types, and PL/pgSQL functions. Each requires a migration strategy.

2

Dump PostgreSQL schema and data

Use pg_dump with --schema-only flag for structure, then data separately.

pg_dump -U postgres --schema-only mydb > schema.sql
pg_dump -U postgres --data-only mydb > data.sql
3

Convert schema to MySQL DDL

Manually convert PostgreSQL DDL to MySQL syntax: change data types, SERIAL → AUTO_INCREMENT, remove PostgreSQL-specific options.

4

Load data into MySQL

Use mysqldump-compatible CSV export from PostgreSQL and mysqlimport, or use an ETL tool like pgloader in reverse.

5

Port functions and triggers

Rewrite PL/pgSQL functions as MySQL stored procedures with different syntax.

Common Issues & Solutions

PostgreSQL ARRAY columns

MySQL has no array type. Options: normalize into a child table, serialize to JSON column, or use a comma-separated TEXT column (not recommended).

TIMESTAMPTZ (timezone-aware) columns

MySQL DATETIME is always naive (no timezone). Store times in UTC in DATETIME columns and handle timezone conversion in the application layer.

ON CONFLICT syntax different

Replace ON CONFLICT (col) DO UPDATE SET c = EXCLUDED.c with ON DUPLICATE KEY UPDATE c = VALUES(c)

Recursive CTEs

MySQL 8.0+ supports recursive CTEs with the same syntax as PostgreSQL. Ensure MySQL 8.0+ is used.

Recommended Migration Tools

pgloader

FREE

Can migrate in both directions. Handles type conversion.

MySQL Workbench

FREE

Schema migration tool with visual ERD editor.

Frequently Asked Questions

What PostgreSQL features are lost when migrating to MySQL?
Key features you lose: JSONB (fast indexable JSON) → downgrade to MySQL JSON. Array column types → must normalize to tables. LATERAL JOINs → rewrite as derived tables. Partial indexes → MySQL doesn't support partial indexes. Full-text search with tsvector → must switch to MySQL FULLTEXT. Timezone-aware timestamps → must handle TZ in application.
Why would someone migrate from PostgreSQL to MySQL?
Common reasons: organizational standardization, specific hosting/SaaS constraints, team familiarity with MySQL tooling, or requirements for specific MySQL features like built-in replication topology or MySQL Cluster. In most cases, PostgreSQL has more features than MySQL for analytics workloads.

Ready to Migrate?

Test your MySQL queries in our free SQL Playground before migrating.

Test in Playground All Migration Guides