Migrating from PostgreSQL to MySQL

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

All examples in this guide 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.

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_general_ci or similar 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 CTEs in MySQL. Window functions: MySQL supports window functions. See Window Functions in MySQL.

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`

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` not recognized	Change to `INT AUTO_INCREMENT`
Array columns failing	Normalize to a child table with a foreign key

Getting Started

Server Development

Extensions

Using Extensions

Creating Extensions

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

Data Type Mapping

Sequences vs AUTO_INCREMENT

SQL Dialect Differences

Features Without MySQL Equivalents

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.

​Data Type Mapping

​Sequences vs AUTO_INCREMENT

​SQL Dialect Differences

​Features Without MySQL Equivalents

​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

Data Type Mapping

Sequences vs AUTO_INCREMENT

SQL Dialect Differences

Features Without MySQL Equivalents

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