Convert PostgreSQL to MySQL: Migration Guide

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

PostgreSQL and MySQL share ANSI SQL syntax but diverge in enough ways that a migration requires careful attention to data types, default behaviors, and SQL dialect differences. Most of the work is schema translation and fixing queries that rely on PostgreSQL-specific features. This guide covers migrating PostgreSQL to MySQL, the common direction when moving onto a MySQL-compatible server such as VillageSQL — an open-source, drop-in replacement for MySQL that adds a PostgreSQL-style extension framework on top. That framework is why this guide has a place for your PostgreSQL extensions to land, not just your schema and data; see Bringing Your Extensions below. If you are moving data the other way, the type mapping and dialect differences below still apply in reverse, but the migration tooling section does not.

Which Approach Should I Use

One-time cutover — dump/restore with pg_dump and LOAD DATA INFILE. See Migration Tools and Migration Approach below.
Need the source database to stay live and writable during migration, or an ongoing sync — set up Debezium with a JDBC sink connector for continuous replication instead of a one-time dump.

Migration Tools

pg_dump + manual schema conversion is the primary path and needs nothing beyond the standard PostgreSQL and MySQL client tools:

# Schema only, plain SQL text
pg_dump -s -f schema.sql mydb

# Data only, as INSERT statements (portable, but slower to load than COPY)
pg_dump -a --inserts -f data.sql mydb

pg_dump’s schema output uses PostgreSQL syntax (SERIAL, BOOLEAN, PostgreSQL-specific CREATE TABLE clauses), so the schema file needs manual translation using the type mapping table above before it will run against MySQL. There is no tool that does this translation automatically and correctly for every case — treat this as the default path for most migrations. Debezium with a JDBC sink connector (debezium.io, Apache-2.0 license, actively maintained) reads PostgreSQL’s write-ahead log and streams changes into MySQL through Kafka Connect, giving continuous replication instead of a one-time copy. This is the right tool when the source database must stay live and writable during migration, or when you need ongoing sync rather than a cutover. It requires a running Kafka and Kafka Connect cluster, which is more infrastructure than a one-off migration needs — for a single cutover, use pg_dump instead.

Data Type Mapping

PostgreSQL type	MySQL equivalent	Notes
`SERIAL` / `BIGSERIAL`	`INT AUTO_INCREMENT` / `BIGINT AUTO_INCREMENT`	MySQL uses AUTO_INCREMENT, not sequences
`TEXT`	`TEXT` or `LONGTEXT`	MySQL TEXT is limited to 65,535 bytes; use LONGTEXT for larger values
`VARCHAR` (no limit)	Not supported	MySQL VARCHAR requires a length; use TEXT instead
`BOOLEAN`	`TINYINT(1)`	MySQL has no true boolean; 0/1 convention
`BYTEA`	`BLOB` or `LONGBLOB`	Binary data
`JSONB`	`JSON`	MySQL JSON is stored as text with validation; no JSONB binary format
`UUID`	`CHAR(36)` or `BINARY(16)`	No native UUID type; see UUIDs in MySQL
`TIMESTAMP WITH TIME ZONE`	`DATETIME` + explicit timezone handling	MySQL DATETIME has no timezone; see Timestamps and Time Zones
`NUMERIC` / `DECIMAL`	`DECIMAL(p,s)`	Precision and scale required in MySQL
`ARRAY`	Not supported	Normalize to a child table
`HSTORE`	`JSON`	Key-value pairs; use JSON in MySQL
`INTERVAL`	Not supported as a column type	Store as INT seconds or use application-side logic

Sequences vs AUTO_INCREMENT

PostgreSQL uses sequences (independent objects) for auto-increment values. MySQL uses AUTO_INCREMENT as a column attribute: PostgreSQL:

CREATE SEQUENCE orders_id_seq;
CREATE TABLE orders (
    id INTEGER DEFAULT nextval('orders_id_seq') PRIMARY KEY
);

MySQL equivalent:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY
);

To get the last inserted ID in MySQL: SELECT LAST_INSERT_ID(); (equivalent to PostgreSQL’s currval() or RETURNING id).

SQL Dialect Differences

String concatenation:

-- PostgreSQL
SELECT first_name || ' ' || last_name FROM users;

-- MySQL
SELECT CONCAT(first_name, ' ', last_name) FROM users;

String quoting:

-- PostgreSQL: double quotes for identifiers, single quotes for strings
SELECT "first_name" FROM users WHERE status = 'active';

-- MySQL: backticks for identifiers, single quotes for strings
SELECT `first_name` FROM users WHERE status = 'active';

MySQL accepts double quotes for identifiers only when ANSI_QUOTES SQL mode is enabled. ILIKE (case-insensitive LIKE):

-- PostgreSQL
SELECT * FROM users WHERE name ILIKE '%alice%';

-- MySQL (case-insensitive by default with ci collations)
SELECT * FROM users WHERE name LIKE '%alice%';

MySQL string comparisons are case-insensitive by default with utf8mb4_0900_ai_ci (the MySQL 8.x default) or similar _ci collations. If you need case-sensitive matching, use a _bin collation. RETURNING clause:

-- PostgreSQL
INSERT INTO users (name) VALUES ('Alice') RETURNING id;

-- MySQL (no RETURNING)
INSERT INTO users (name) VALUES ('Alice');
SELECT LAST_INSERT_ID();

LIMIT / OFFSET:

-- PostgreSQL
SELECT * FROM orders LIMIT 10 OFFSET 20;

-- MySQL (same syntax)
SELECT * FROM orders LIMIT 10 OFFSET 20;
-- or shorthand:
SELECT * FROM orders LIMIT 20, 10;  -- LIMIT offset, count

Schemas vs databases: PostgreSQL uses schemas within a database (mydb.public.orders). MySQL uses “schemas” and “databases” interchangeably — there’s no concept of a schema within a database. What PostgreSQL calls a schema, MySQL calls a database. NULL handling in UNIQUE indexes: PostgreSQL allows multiple NULL values in a unique column (NULLs are not equal to each other). MySQL (InnoDB) also allows multiple NULLs in unique indexes — behavior is the same. CTEs: MySQL supports CTEs including recursive CTEs. See MySQL common table expressions. Window functions: MySQL supports window functions. See Window Functions in MySQL. Stored procedures and functions: PostgreSQL writes procedural logic in PL/pgSQL, with exception handling and rich control flow. MySQL uses its own procedural SQL dialect, has no PL/pgSQL support, and requires a DELIMITER change in client tools so the semicolons inside the procedure body aren’t parsed as the end of the statement:

-- MySQL: a stored procedure with an OUT parameter
DELIMITER ;;
CREATE PROCEDURE total_orders(IN cust_id INT, OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM orders WHERE customer_id = cust_id;
END;;
DELIMITER ;

CALL total_orders(1, @t);
SELECT @t;

There is no automated translation from PL/pgSQL to MySQL’s procedural syntax — rewrite each procedure by hand, and expect exception-handling logic (EXCEPTION WHEN ... blocks) to need the most rework, since MySQL’s DECLARE ... HANDLER works differently. See Stored Procedures in MySQL. Case sensitivity of identifiers: PostgreSQL folds unquoted identifiers to lowercase and treats quoted identifiers ("MyTable") as case-sensitive. MySQL’s rule is different: whether database and table names are case-sensitive depends on the lower_case_table_names system variable and the underlying filesystem, not on quoting. Column, index, and stored routine names are never case-sensitive for lookup purposes, regardless of platform. Don’t assume a schema that relied on case-sensitive PostgreSQL table names will behave identically after migration — check lower_case_table_names on the target server.

Features Without MySQL Equivalents

Some PostgreSQL features have no direct equivalent:

PostgreSQL feature	MySQL situation
Arrays as column types	Normalize to a child table
Table inheritance	Not supported; use separate tables
Custom operators	Not supported
Full `LISTEN`/`NOTIFY`	Not supported; use application-side polling or a message queue
Partial indexes (`WHERE` clause on index)	Not supported; use generated columns to index a subset
Materialized views	Not supported natively; approximate with tables + stored procedures
`COPY` command for bulk load	Use `LOAD DATA INFILE` or MySQL Shell’s `loadDump`

Bringing Your Extensions

PostgreSQL relies on extensions like pgcrypto, pg_trgm, and uuid-ossp for functionality it doesn’t ship natively. Stock MySQL has no equivalent mechanism for adding this kind of functionality — but VillageSQL does: the VillageSQL Extension Framework (VEF) lets you install custom SQL functions and types into the server with INSTALL EXTENSION. Some of VillageSQL’s extensions are direct ports of the PostgreSQL extensions above:

PostgreSQL extension	VillageSQL extension	Covers
`pgcrypto`	`vsql_crypto`	Hashing, HMAC, AES encryption, password hashing (PBKDF2 only — bcrypt hashes don’t carry over). See Hashing Data in MySQL.
`uuid-ossp`	`vsql_uuid`	A native UUID column type plus v1–v7 generation functions. See UUIDs in MySQL.
— (built-in `inet`/`cidr`/`macaddr` types)	`vsql_network_address`	IPv4, IPv6, and MAC address types. See Storing IP Addresses in MySQL.
`pg_trgm`	`vsql_trgm`	Trigram-based string similarity search. See Trigram Similarity in MySQL.

If your PostgreSQL database depends on an extension not listed here, it may not have a VillageSQL equivalent yet — check the extension catalog in the VillageSQL docs before assuming the functionality is gone for good. And if the dependency is a custom PostgreSQL extension your own team wrote, VEF is also the tool for porting it: the vsql-extension-builder skill walks an AI coding agent through rebuilding a custom extension on VillageSQL, from requirements through a tested, documented .veb package.

Migration Approach

Export the schema from PostgreSQL (pg_dump --schema-only) and translate each table manually, using the type mapping above.
Export the data from PostgreSQL as CSV (COPY table TO '/tmp/table.csv' CSV HEADER).
Load the data into MySQL with LOAD DATA INFILE or mysqlimport.
Test queries — find all PostgreSQL-specific syntax and rewrite it.
Verify counts and checksums — SELECT COUNT(*) on every table; spot-check key rows.

Frequently Asked Questions

Does MySQL support `UPSERT` like PostgreSQL’s `ON CONFLICT`?

Yes, using different syntax. See UPSERT in MySQL. MySQL’s INSERT ... ON DUPLICATE KEY UPDATE and REPLACE INTO cover the same use case as PostgreSQL’s ON CONFLICT DO UPDATE and ON CONFLICT DO NOTHING.

How do I handle PostgreSQL’s `BOOLEAN` columns in MySQL?

Use TINYINT(1). Store 1 for true and 0 for false. Most MySQL client libraries and ORMs handle this automatically and present TINYINT(1) columns as booleans. You can also use BIT(1) but TINYINT(1) has broader tooling support.

Troubleshooting

Problem	Solution
Syntax errors on `		` concatenation	Replace with `CONCAT()`
`ILIKE` not recognized	Use `LIKE` (case-insensitive by default with ci collations)
Double-quoted identifiers not working	Enable `ANSI_QUOTES` SQL mode or switch to backtick quoting
`RETURNING` clause not working	Use `LAST_INSERT_ID()` after INSERT; there’s no MySQL equivalent
`SERIAL` behaves differently than in Postgres	MySQL does accept `SERIAL` — it is an alias for `BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE`, wider than Postgres’s `SERIAL` and carrying an implicit unique key. Use explicit `INT AUTO_INCREMENT` for the narrower Postgres equivalent
Array columns failing	Normalize to a child table with a foreign key

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

Which Approach Should I Use

Migration Tools

Data Type Mapping

Sequences vs AUTO_INCREMENT

SQL Dialect Differences

Features Without MySQL Equivalents

Bringing Your Extensions

Migration Approach

Frequently Asked Questions

Does MySQL support `UPSERT` like PostgreSQL’s `ON CONFLICT`?

How do I handle PostgreSQL’s `BOOLEAN` columns in MySQL?

Troubleshooting

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​Which Approach Should I Use

​Migration Tools

​Data Type Mapping

​Sequences vs AUTO_INCREMENT

​SQL Dialect Differences

​Features Without MySQL Equivalents

​Bringing Your Extensions

​Migration Approach

​Frequently Asked Questions

​Does MySQL support UPSERT like PostgreSQL’s ON CONFLICT?

​How do I handle PostgreSQL’s BOOLEAN columns in MySQL?

​Troubleshooting

​See also

Which Approach Should I Use

Migration Tools

Data Type Mapping

Sequences vs AUTO_INCREMENT

SQL Dialect Differences

Features Without MySQL Equivalents

Bringing Your Extensions

Migration Approach

Frequently Asked Questions

Does MySQL support `UPSERT` like PostgreSQL’s `ON CONFLICT`?

How do I handle PostgreSQL’s `BOOLEAN` columns in MySQL?

Troubleshooting

See also