MySQL to PostgreSQL Converter: Syntax Guide & Examples

Why Convert MySQL to PostgreSQL?

PostgreSQL has become the database of choice for teams that need advanced features like JSONB indexing, window functions, CTEs with write operations, and full ACID compliance with no caveats. If you are running MySQL and hitting limitations around data integrity, extensibility, or standards compliance, migrating to PostgreSQL is a natural next step.

Many cloud platforms now default to PostgreSQL-compatible engines. AWS Aurora PostgreSQL, Google Cloud SQL, and Supabase all run PostgreSQL under the hood. Moving your queries and schemas from MySQL to PostgreSQL opens up these platforms without vendor lock-in.

The two databases share the SQL standard but differ in quoting, data types, auto-increment handling, and built-in functions. This guide covers every difference you will encounter during migration.

Key Syntax Differences

Feature	MySQL	PostgreSQL
Auto-increment	AUTO_INCREMENT	SERIAL / GENERATED ALWAYS AS IDENTITY
Identifier quoting	`backticks`	"double quotes"
String concatenation	CONCAT(a, b)	a || b
Current timestamp	NOW() / CURRENT_TIMESTAMP	NOW() / CURRENT_TIMESTAMP
NULL-safe comparison	IFNULL(a, b)	COALESCE(a, b)
LIMIT with offset	LIMIT 10 OFFSET 20	LIMIT 10 OFFSET 20 (same)
Boolean type	TINYINT(1)	BOOLEAN (true/false)
ENUM type	Inline ENUM('a','b')	CREATE TYPE ... AS ENUM
UNSIGNED integer	INT UNSIGNED	Not supported (use CHECK constraint)
Full-text search	MATCH ... AGAINST	to_tsvector / to_tsquery

Common Conversions with Examples

AUTO_INCREMENT to SERIAL

MySQL uses AUTO_INCREMENT as a column attribute. PostgreSQL uses the SERIAL pseudo-type, which creates an implicit sequence behind the scenes.

-- MySQL
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- PostgreSQL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

For PostgreSQL 10+, you can also use the SQL-standard syntax:

-- PostgreSQL 10+ (SQL standard)
CREATE TABLE users (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

String Concatenation

MySQL uses the CONCAT() function. PostgreSQL supports both CONCAT() and the || operator, but the operator is idiomatic.

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

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

Note: In PostgreSQL, || returns NULL if any operand is NULL. Use CONCAT() if you want NULL-safe concatenation.

Date Function Differences

MySQL and PostgreSQL handle date arithmetic differently. MySQL uses DATE_ADD and DATE_SUB; PostgreSQL uses interval arithmetic.

-- MySQL
SELECT DATE_ADD(created_at, INTERVAL 30 DAY) FROM orders;
SELECT DATEDIFF(end_date, start_date) FROM projects;

-- PostgreSQL
SELECT created_at + INTERVAL '30 days' FROM orders;
SELECT end_date - start_date FROM projects;  -- returns an interval

For extracting parts of a date, MySQL uses dedicated functions while PostgreSQL uses EXTRACT:

-- MySQL
SELECT YEAR(created_at), MONTH(created_at) FROM orders;

-- PostgreSQL
SELECT EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at) FROM orders;

LIMIT and OFFSET

Both databases support LIMIT ... OFFSET with identical syntax, so no changes are needed for pagination queries. However, MySQL also supports the shorthand LIMIT offset, count which PostgreSQL does not.

-- MySQL shorthand (NOT valid in PostgreSQL)
SELECT * FROM products LIMIT 20, 10;

-- Standard syntax (works in both)
SELECT * FROM products LIMIT 10 OFFSET 20;

Boolean Handling

MySQL stores booleans as TINYINT(1) with values 0 and 1. PostgreSQL has a native BOOLEAN type with TRUE and FALSE literals.

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

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

IFNULL to COALESCE

MySQL's IFNULL() takes exactly two arguments. PostgreSQL uses COALESCE(), which accepts any number of arguments and returns the first non-NULL value.

-- MySQL
SELECT IFNULL(nickname, name) AS display_name FROM users;

-- PostgreSQL
SELECT COALESCE(nickname, name) AS display_name FROM users;

COALESCE is also valid in MySQL, so using it everywhere makes your queries more portable. See our guide on SQL fundamentals for more on NULL handling.

Data Type Mapping

MySQL Type	PostgreSQL Type	Notes
TINYINT	SMALLINT	No TINYINT in PostgreSQL
TINYINT(1)	BOOLEAN	Used as boolean in MySQL
INT	INTEGER	Same range
INT UNSIGNED	BIGINT	Or use CHECK (col >= 0)
BIGINT	BIGINT	Same
FLOAT	REAL	4-byte floating point
DOUBLE	DOUBLE PRECISION	8-byte floating point
DATETIME	TIMESTAMP	PostgreSQL TIMESTAMP has microsecond precision
TEXT	TEXT	Same
BLOB	BYTEA	Binary data
ENUM('a','b')	user-defined ENUM	Requires CREATE TYPE first
JSON	JSONB	JSONB is indexed and faster for queries
VARCHAR(255)	VARCHAR(255)	Same, but PostgreSQL also offers unlimited TEXT
SET	TEXT[] or JSONB	No SET type in PostgreSQL

Step-by-Step Migration Guide

1. Export your MySQL schema. Run mysqldump --no-data your_database > schema.sql to get the DDL statements without data.
2. Convert the schema. Replace AUTO_INCREMENT with SERIAL, backticks with double quotes (or remove them), TINYINT(1) with BOOLEAN, DOUBLE with DOUBLE PRECISION, and remove ENGINE=InnoDB clauses.
3. Export the data. Use mysqldump --no-create-info --compatible=ansi your_database > data.sql for a more PostgreSQL-friendly dump. Alternatively, export as CSV and use PostgreSQL's COPY command.
4. Update your queries. Search for MySQL-specific functions (IFNULL, GROUP_CONCAT, DATE_ADD) and replace them with PostgreSQL equivalents (COALESCE, STRING_AGG, interval arithmetic).
5. Test with a staging database. Load the converted schema and data into a test PostgreSQL instance. Run your application test suite to catch any remaining incompatibilities.
6. Switch production. Once tests pass, update your connection strings and deploy. Keep the MySQL instance running in read-only mode during the transition period.

For more on SQL fundamentals, see our What is SQL guide. If you are working with joins during migration, check our SQL Joins Explained tutorial.

Convert Automatically with AI2SQL

Instead of manually translating every query, you can paste your MySQL query into the SQL Converter tool and get correct PostgreSQL syntax automatically, whether you need a simple SELECT or a complex multi-table join with window functions.

This is especially useful during migration, when you have hundreds of queries to convert and cannot afford to miss a subtle syntax difference. Try the converter free and generate your first PostgreSQL query in seconds.

Looking for the reverse direction? See our PostgreSQL to MySQL converter. You can also explore all available SQL converter tools.

Frequently Asked Questions

Can I convert MySQL queries to PostgreSQL automatically?

Yes. Tools like AI2SQL let you describe what you need in plain English and generate the correct PostgreSQL syntax automatically. For manual conversion, the main changes are replacing AUTO_INCREMENT with SERIAL, backtick quoting with double quotes, and MySQL-specific functions like IFNULL with PostgreSQL equivalents like COALESCE.

What is the PostgreSQL equivalent of MySQL AUTO_INCREMENT?

PostgreSQL uses SERIAL (or BIGSERIAL for large tables) instead of AUTO_INCREMENT. In PostgreSQL 10+, you can also use GENERATED ALWAYS AS IDENTITY, which follows the SQL standard. SERIAL creates an implicit sequence, while IDENTITY gives you more control over the sequence behavior.

How do I handle MySQL ENUM types in PostgreSQL?

PostgreSQL supports ENUM types but the syntax is different. In MySQL, ENUM is defined inline: column_name ENUM('val1','val2'). In PostgreSQL, you first create the type with CREATE TYPE status_type AS ENUM ('val1','val2'), then use it: column_name status_type. Alternatively, use a VARCHAR with a CHECK constraint for simpler cases.