> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Convert PostgreSQL to MySQL: Migration Guide

> How to migrate a PostgreSQL database to MySQL: data type mapping, AUTO_INCREMENT vs sequences, SQL dialect differences, and migration pitfalls to avoid.

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> 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](https://villagesql.com/install) — 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](#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](#migration-tools) and [Migration Approach](#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:

```bash theme={null}
# 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 below 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](https://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](https://villagesql.com/docs/en/guides/uuids.md) |
| `TIMESTAMP WITH TIME ZONE` | `DATETIME` + explicit timezone handling | MySQL DATETIME has no timezone; see [Timestamps and Time Zones](https://villagesql.com/docs/en/guides/timestamps-timezones.md) |
| `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:**

```sql theme={null}
CREATE SEQUENCE orders_id_seq;
CREATE TABLE orders (
    id INTEGER DEFAULT nextval('orders_id_seq') PRIMARY KEY
);
```

**MySQL equivalent:**

```sql theme={null}
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:**

```sql theme={null}
-- PostgreSQL
SELECT first_name || ' ' || last_name FROM users;

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

**String quoting:**

```sql theme={null}
-- 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):**

```sql theme={null}
-- 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:**

```sql theme={null}
-- PostgreSQL
INSERT INTO users (name) VALUES ('Alice') RETURNING id;

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

**LIMIT / OFFSET:**

```sql theme={null}
-- 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](https://villagesql.com/docs/en/tutorial/cte.md).

**Window functions:**
MySQL supports window functions. See [Window Functions in MySQL](https://villagesql.com/docs/en/guides/window-functions.md).

**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:

```sql theme={null}
-- 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](https://villagesql.com/docs/en/guides/stored-procedures.md).

**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](https://villagesql.com/docs/en/guides/hashing-data.md). |
| `uuid-ossp` | `vsql_uuid` | A native UUID column type plus v1–v7 generation functions. See [UUIDs in MySQL](https://villagesql.com/docs/en/guides/uuids.md). |
| — (built-in `inet`/`cidr`/`macaddr` types) | `vsql_network_address` | IPv4, IPv6, and MAC address types. See [Storing IP Addresses in MySQL](https://villagesql.com/docs/en/guides/storing-ip-addresses.md). |
| `pg_trgm` | `vsql_trgm` | Trigram-based string similarity search. See [Trigram Similarity in MySQL](https://villagesql.com/docs/en/guides/trigram-similarity.md). |

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`](https://github.com/villagesql/villagesql-skills/tree/main/skills/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

1. **Export the schema** from PostgreSQL (`pg_dump --schema-only`) and translate each table manually, using the type mapping above.
2. **Export the data** from PostgreSQL as CSV (`COPY table TO '/tmp/table.csv' CSV HEADER`).
3. **Load the data** into MySQL with `LOAD DATA INFILE` or `mysqlimport`.
4. **Test queries** — find all PostgreSQL-specific syntax and rewrite it.
5. **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](https://villagesql.com/docs/en/guides/upsert.md). 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 | | |

## See also

* [Schema Migrations in MySQL](https://villagesql.com/docs/en/guides/schema-migrations.md) — running the DDL changes a migration requires
* [Choosing MySQL Data Types](https://villagesql.com/docs/en/guides/choosing-data-types.md) — mapping PostgreSQL types to MySQL equivalents
* [MySQL vs PostgreSQL: Which Should You Use?](https://villagesql.com/docs/en/guides/mysql-vs-postgresql.md) — the feature-by-feature comparison behind the migration


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.