PostgreSQL to MySQL Converter: Syntax Guide & Examples

Why Convert PostgreSQL to MySQL?

While PostgreSQL is known for advanced features, MySQL remains the most widely deployed relational database. Many hosting providers, CMS platforms (WordPress, Drupal), and legacy systems run on MySQL. If your application needs to integrate with these ecosystems, converting from PostgreSQL to MySQL may be the pragmatic choice.

MySQL also has advantages in read-heavy workloads with simple queries, and its replication setup is often simpler. Some teams move to MySQL for better compatibility with managed services like PlanetScale or AWS RDS MySQL, which offer specific performance and scaling features.

The conversion requires attention to PostgreSQL-specific features that have no direct MySQL equivalent, such as arrays, custom types, and advanced JSONB operations. This guide covers every difference you will encounter.

Key Syntax Differences

Feature	PostgreSQL	MySQL
Auto-increment	SERIAL / IDENTITY	AUTO_INCREMENT
Identifier quoting	"double quotes"	`backticks`
String concatenation	a || b	CONCAT(a, b)
Boolean type	BOOLEAN (TRUE/FALSE)	TINYINT(1) (1/0)
Array types	INT[], TEXT[]	Not supported (use JSON)
JSON binary	JSONB	JSON (text-based)
String aggregation	STRING_AGG(col, ',')	GROUP_CONCAT(col)
Date interval	col + INTERVAL '30 days'	DATE_ADD(col, INTERVAL 30 DAY)
UPSERT	ON CONFLICT ... DO UPDATE	ON DUPLICATE KEY UPDATE
FULL OUTER JOIN	Supported natively	Emulate with UNION of LEFT + RIGHT JOIN

Common Conversions with Examples

SERIAL to AUTO_INCREMENT

PostgreSQL's SERIAL is a shorthand that creates a sequence. In MySQL, use AUTO_INCREMENT on the column directly.

-- PostgreSQL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
);

-- MySQL
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
);

String Concatenation

PostgreSQL uses the || operator. MySQL requires the CONCAT() function because || is treated as logical OR in MySQL by default.

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

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

Date Function Differences

-- PostgreSQL
SELECT created_at + INTERVAL '7 days' FROM orders;
SELECT EXTRACT(EPOCH FROM created_at) FROM orders;
SELECT DATE_TRUNC('month', created_at) FROM orders;

-- MySQL
SELECT DATE_ADD(created_at, INTERVAL 7 DAY) FROM orders;
SELECT UNIX_TIMESTAMP(created_at) FROM orders;
SELECT DATE_FORMAT(created_at, '%Y-%m-01') FROM orders;

Boolean Handling

-- PostgreSQL
CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    done BOOLEAN DEFAULT FALSE
);
SELECT * FROM tasks WHERE done IS TRUE;

-- MySQL
CREATE TABLE tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    done TINYINT(1) DEFAULT 0
);
SELECT * FROM tasks WHERE done = 1;

Array Types to JSON

PostgreSQL supports native array columns. MySQL has no array type, so you convert to JSON.

-- PostgreSQL
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    tags TEXT[] DEFAULT '{}'
);
SELECT * FROM articles WHERE 'sql' = ANY(tags);

-- MySQL
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tags JSON DEFAULT ('[]')
);
SELECT * FROM articles WHERE JSON_CONTAINS(tags, '"sql"');

UPSERT Syntax

-- PostgreSQL
INSERT INTO settings (key, value)
VALUES ('theme', 'dark')
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;

-- MySQL
INSERT INTO settings (`key`, value)
VALUES ('theme', 'dark')
ON DUPLICATE KEY UPDATE value = VALUES(value);

Data Type Mapping

PostgreSQL Type	MySQL Type	Notes
SERIAL	INT AUTO_INCREMENT	Add PRIMARY KEY
BIGSERIAL	BIGINT AUTO_INCREMENT	For large tables
BOOLEAN	TINYINT(1)	0/1 instead of TRUE/FALSE
TEXT	TEXT or LONGTEXT	MySQL TEXT has 64KB limit
BYTEA	BLOB	Binary data
JSONB	JSON	No binary storage in MySQL
TIMESTAMP	DATETIME	MySQL TIMESTAMP has 2038 limit
DOUBLE PRECISION	DOUBLE	Same precision
REAL	FLOAT	4-byte float
UUID	CHAR(36)	No native UUID in MySQL
TEXT[]	JSON	Store as JSON array
INET	VARCHAR(45)	No native IP type in MySQL
INTERVAL	No equivalent	Use INT for seconds or VARCHAR

Step-by-Step Migration Guide

1. Export your PostgreSQL schema. Run pg_dump --schema-only your_database > schema.sql to get DDL statements.
2. Convert the schema. Replace SERIAL with INT AUTO_INCREMENT, BOOLEAN with TINYINT(1), BYTEA with BLOB, JSONB with JSON, and array columns with JSON. Remove PostgreSQL-specific features like CREATE TYPE for ENUMs (use inline ENUM() in MySQL).
3. Export the data. Use pg_dump --data-only --inserts your_database > data.sql for INSERT-based export. Convert TRUE/FALSE literals to 1/0 and array literals {a,b} to JSON ["a","b"].
4. Update your queries. Replace || with CONCAT(), STRING_AGG with GROUP_CONCAT, interval arithmetic with DATE_ADD/DATE_SUB, and ON CONFLICT with ON DUPLICATE KEY UPDATE.
5. Test thoroughly. Pay special attention to queries using FULL OUTER JOIN (not supported in MySQL), CTEs with write operations (MySQL 8.0+ supports read-only CTEs), and lateral joins.
6. Deploy and monitor. Run both databases in parallel during transition. Compare query results to catch edge cases around NULL handling and type coercion.

Need help with SQL joins during your migration? Check our visual guide for syntax that works across databases.

Convert Automatically with AI2SQL

Manually converting hundreds of queries is tedious and error-prone. With the SQL Converter tool, you paste your PostgreSQL query, select MySQL as your target database, and get the correct syntax generated automatically. No need to remember whether MySQL uses GROUP_CONCAT or STRING_AGG.

Try the converter free and convert your first query in seconds. Looking for the reverse? See MySQL to PostgreSQL. Browse all converter tools.

Frequently Asked Questions

How do I convert PostgreSQL SERIAL to MySQL?

Replace SERIAL or BIGSERIAL with INT AUTO_INCREMENT (or BIGINT AUTO_INCREMENT). PostgreSQL's GENERATED ALWAYS AS IDENTITY also maps to AUTO_INCREMENT in MySQL. The AUTO_INCREMENT column must be part of a key in MySQL.

What happens to PostgreSQL array columns in MySQL?

MySQL does not have native array types. You need to convert PostgreSQL arrays to either a JSON column (using MySQL's JSON type), a comma-separated VARCHAR, or a separate junction table for proper normalization. The JSON approach is usually the simplest migration path.

Does MySQL support PostgreSQL's JSONB features?

MySQL has a JSON type but it stores data as text internally, unlike PostgreSQL's JSONB which stores binary. MySQL supports JSON path expressions with -> and ->> operators (similar to PostgreSQL), but lacks GIN indexing on JSON. For complex JSON queries, you may need to restructure your schema or use generated columns with indexes.