MySQL vs PostgreSQL: SQL Syntax Differences You Need to Know

MySQL and PostgreSQL are the two most popular open-source databases. They both speak SQL — but with enough syntax differences to break your queries when you switch between them. Here is what actually changes.

The Most Common Differences

1. Identifier Quoting

MySQL wraps table and column names in backticks. PostgreSQL uses double quotes.

MySQL:

SELECT `user_id`, `email` FROM `users`;

PostgreSQL:

SELECT "user_id", "email" FROM "users";

Standard SQL (and SQLite, BigQuery) use double quotes too — so MySQL is the odd one out here.

---

2. Auto-Increment Columns

MySQL:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

PostgreSQL:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100)
);

PostgreSQL also supports the SQL standard version: GENERATED ALWAYS AS IDENTITY.

---

3. Boolean Type

MySQL has no true boolean — it uses TINYINT(1) where 1 = true and 0 = false:

is_active TINYINT(1) DEFAULT 1

PostgreSQL has a native BOOLEAN type:

is_active BOOLEAN DEFAULT TRUE

---

4. Insert and Ignore Duplicates

MySQL:

INSERT IGNORE INTO users (email) VALUES ('test@example.com');

PostgreSQL:

INSERT INTO users (email) VALUES ('test@example.com')
ON CONFLICT DO NOTHING;

PostgreSQL's ON CONFLICT is more powerful — you can also do upserts:

INSERT INTO users (email, name) VALUES ('test@example.com', 'Mani')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

---

5. String Functions

Function	MySQL	PostgreSQL
Concatenate	CONCAT(a, b)	`a
Null fallback	IFNULL(val, 0)	COALESCE(val, 0)
String aggregate	GROUP_CONCAT(col)	STRING_AGG(col, ',')
Case-insensitive LIKE	LIKE (case-insensitive by default on utf8)	ILIKE

---

6. Current Timestamp

Both MySQL and PostgreSQL support NOW() and CURRENT_TIMESTAMP.

SQL Server uses GETDATE(). SQLite uses datetime('now').

---

7. LIMIT Syntax

MySQL, PostgreSQL, SQLite, and BigQuery all use LIMIT:

SELECT * FROM users LIMIT 10 OFFSET 20;

SQL Server is the exception — it uses TOP:

SELECT TOP 10 * FROM users;

---

Quick Reference Table

Feature	MySQL	PostgreSQL
Identifiers	backticks	double quotes
Auto-increment	INT AUTO_INCREMENT	SERIAL
Boolean	TINYINT(1)	BOOLEAN
Skip duplicates	INSERT IGNORE	ON CONFLICT DO NOTHING
Null fallback	IFNULL()	COALESCE()
String concat	CONCAT(a, b)	`a
Case-insensitive match	LIKE	ILIKE
Limit rows	LIMIT n	LIMIT n

---

Convert Your SQL Instantly

Migrating from MySQL to PostgreSQL (or any direction)? Use our free SQL Dialect Converter to transform your queries automatically. Supports MySQL, PostgreSQL, SQLite, SQL Server, and BigQuery. Paste your query, pick your target database, and get a converted query in seconds. No login. No upload. 100% browser-based.