PostgreSQL vs MySQL SQL Syntax Differences

Most of the PostgreSQL vs MySQL syntax you write every day is identical. SELECT, JOIN, WHERE, GROUP BY, and basic INSERT work the same in both. The trouble is the other 20 percent, the dialect-specific corners where a query that runs clean on MySQL throws a syntax error on Postgres (and vice versa). This guide maps those corners with side-by-side examples so you can port queries without the trial-and-error.

If you are migrating a database, writing a query that has to run on both engines, or just trying to understand why a Stack Overflow snippet does not work in your console, the answer is almost always one of a handful of known differences. UPSERT, booleans, set operators, auto-increment, and string handling cause the overwhelming majority of porting pain.

PostgreSQL vs MySQL Syntax: The 20% That Actually Breaks#

Business-level comparisons of these two databases focus on performance, licensing, and replication. Useful for picking a stack, useless when you have a broken query in front of you. What you need is the syntax table.

Here is the short version of where the dialects diverge. The rest of this guide expands each row with real examples.

Feature	PostgreSQL	MySQL
UPSERT	INSERT ... ON CONFLICT ... DO UPDATE	INSERT ... ON DUPLICATE KEY UPDATE
Auto-increment	SERIAL / GENERATED ... AS IDENTITY	AUTO_INCREMENT
Boolean	native BOOLEAN (true/false)	TINYINT(1) alias, stores 0/1
Set operators	INTERSECT, EXCEPT supported	INTERSECT/EXCEPT only in 8.0.31+
String concat	|| operator	CONCAT() (|| means OR)
Case sensitivity	identifiers fold to lowercase	depends on OS and table config
Quotes	double quotes for identifiers	backticks for identifiers
LIMIT/OFFSET	LIMIT n OFFSET m	LIMIT m, n or LIMIT n OFFSET m

Quick rule of thumb: if a query fails after a migration, check UPSERT, boolean comparisons, and identifier quoting first. Those three account for most of the breakage.

UPSERT: ON CONFLICT vs ON DUPLICATE KEY UPDATE#

This is the single biggest gotcha when porting queries, and it has no clean automatic translation. Both engines let you insert a row and update it if a key already exists, but the syntax is completely different.

MySQL uses ON DUPLICATE KEY UPDATE, which fires on any unique or primary key collision:

INSERT INTO users (id, email, login_count)
VALUES (1, 'a@example.com', 1)
ON DUPLICATE KEY UPDATE login_count = login_count + 1;

PostgreSQL uses ON CONFLICT, and it forces you to name the conflict target (the column or constraint you expect to collide on):

INSERT INTO users (id, email, login_count)
VALUES (1, 'a@example.com', 1)
ON CONFLICT (id) DO UPDATE
SET login_count = users.login_count + 1;

Two things trip people up here. First, Postgres requires the conflict target, while MySQL infers it from whatever unique key was violated. Second, the way you reference the incoming row differs. MySQL exposes the new values through VALUES(col) (older syntax) or a row alias, whereas Postgres exposes them through the special EXCLUDED pseudo-table:

-- PostgreSQL referencing the incoming value
ON CONFLICT (id) DO UPDATE
SET login_count = EXCLUDED.login_count;

If you only remember one difference from this article, make it this one. UPSERT is where the most queries silently fail to translate.

Auto-Increment: SERIAL and IDENTITY vs AUTO_INCREMENT#

Auto-incrementing primary keys exist in both engines, but they are declared differently.

MySQL attaches AUTO_INCREMENT to the column:

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  total DECIMAL(10,2)
);

PostgreSQL has two approaches. The older, widely seen one is SERIAL, which is shorthand that creates a sequence behind the scenes:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  total NUMERIC(10,2)
);

The modern, standard-SQL-compliant approach (recommended on Postgres 10 and later) is GENERATED ... AS IDENTITY:

CREATE TABLE orders (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  total NUMERIC(10,2)
);

A subtle related difference: MySQL's DECIMAL and Postgres's NUMERIC are functionally the same arbitrary-precision type, and both accept either keyword. But NUMERIC is the standard-SQL name you will see in Postgres documentation, so do not be surprised when porting goes the other way.

Booleans: Native BOOLEAN vs TINYINT(1)#

PostgreSQL has a real BOOLEAN type that stores true and false (and NULL). You can compare it directly:

SELECT * FROM users WHERE is_active = true;
SELECT * FROM users WHERE is_active;  -- also valid in Postgres

MySQL has no native boolean. BOOLEAN and BOOL are aliases for TINYINT(1), which stores integers. true maps to 1 and false maps to 0. That mostly works, but it leaks in two places:

• Comparisons against the literals true/false work because MySQL treats them as 1/0, but your stored data is integers, so a SELECT returns 1 and 0, not true and false.
• Any value other than 0 is truthy in MySQL's integer world, so TINYINT(1) columns can technically hold 2, 5, or -1 if your application is careless.

When you port a Postgres boolean column to MySQL, expect the data to surface as 0/1 and adjust any application code or ORM mapping that expected the strings true/false.

Set Operators: INTERSECT and EXCEPT#

PostgreSQL has full support for the standard set operators: UNION, UNION ALL, INTERSECT, and EXCEPT. They all behave as the SQL standard describes.

SELECT id FROM table_a
INTERSECT
SELECT id FROM table_b;

MySQL was the laggard here for years. UNION and UNION ALL have always worked, but INTERSECT and EXCEPT only arrived in MySQL 8.0.31. If you target an older MySQL version, you cannot use them at all and have to rewrite the logic.

The classic MySQL workaround for INTERSECT is an inner join or an IN subquery:

-- INTERSECT rewritten for older MySQL
SELECT DISTINCT a.id
FROM table_a a
JOIN table_b b ON a.id = b.id;

And for EXCEPT, a LEFT JOIN ... IS NULL or a NOT IN / NOT EXISTS pattern:

-- EXCEPT rewritten for older MySQL
SELECT a.id
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id
WHERE b.id IS NULL;

Watch out for NULL handling. NOT IN with a subquery that returns any NULL value can produce an empty result set unexpectedly. NOT EXISTS and the LEFT JOIN ... IS NULL pattern are safer when NULLs are possible.

Strings, Quotes, and Concatenation#

This category causes the most baffling errors because the same characters mean different things.

Identifier quoting#

PostgreSQL uses double quotes for identifiers (table and column names) and single quotes for string literals:

SELECT "userName" FROM "Users" WHERE name = 'Alice';

MySQL uses backticks for identifiers by default:

SELECT `userName` FROM `Users` WHERE name = 'Alice';

MySQL can be told to accept double-quoted identifiers by enabling ANSI_QUOTES mode, but out of the box, double quotes around a string can behave like a string literal, which leads to confusing bugs when you copy Postgres SQL into a MySQL console.

String concatenation#

PostgreSQL uses the standard || operator to concatenate strings:

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

In MySQL, || means logical OR by default, not concatenation. You must use the CONCAT() function:

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

This is a silent failure waiting to happen. A || concatenation copied from Postgres will not error in MySQL; it will quietly evaluate as a boolean OR and return 0 or 1, which is far harder to debug than a syntax error.

Case sensitivity#

PostgreSQL folds unquoted identifiers to lowercase, so MyTable and mytable are the same object unless you quote them. MySQL's identifier case sensitivity depends on the operating system and the lower_case_table_names setting, which means a query can work on a developer's Mac and fail on a Linux production server. When in doubt, pick a consistent lowercase-with-underscores naming convention and you sidestep the whole problem.

LIMIT, OFFSET, and Pagination#

Both engines support LIMIT n OFFSET m, which is the portable form to prefer:

SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

MySQL also accepts a comma shorthand that PostgreSQL does not understand:

-- MySQL only: skip 20, take 10
SELECT * FROM products ORDER BY id LIMIT 20, 10;

Note the order is reversed in the comma form (offset first, then count), which is a common source of off-by-page errors. Stick with the explicit LIMIT ... OFFSET ... syntax and your pagination queries port cleanly in both directions.

Porting Queries Without the Guesswork#

When you move a query between these dialects, work through the high-risk features in order rather than running it blindly and reading error messages. A practical checklist:

• Replace UPSERT syntax (ON DUPLICATE KEY UPDATE to ON CONFLICT, or the reverse) and fix the row reference (VALUES() to EXCLUDED).
• Swap || string concatenation for CONCAT() when targeting MySQL.
• Convert identifier quoting (backticks to double quotes, or the reverse).
• Check boolean columns and expect 0/1 data on MySQL.
• Replace INTERSECT/EXCEPT with joins if you target MySQL before 8.0.31.
• Normalize auto-increment declarations (SERIAL/IDENTITY versus AUTO_INCREMENT).

Reading a dense query to find these spots is much easier once it is formatted. A wall of single-line SQL hides the ON CONFLICT clause and the stray ||. Our multi-dialect SQL formatter reformats and indents queries for both PostgreSQL and MySQL, so you can see the structure clearly before you start translating. For a deeper look at how formatting differs across engines, see our guide on how to format a SQL query for readability across dialects.

If your work also involves shipping query results to an API or config, the same readability principle applies to your payloads. Our JSON formatter cleans up the data layer, and the walkthrough on how to format JSON in JavaScript covers the JSON.stringify patterns that pair with database output.

The Bottom Line on PostgreSQL vs MySQL Syntax#

The PostgreSQL vs MySQL syntax differences are concentrated, not scattered. Standard SELECT, JOIN, and aggregation are portable; the breakage clusters around UPSERT, booleans, set operators, string concatenation, identifier quoting, and auto-increment. Learn those six and you can port the vast majority of real-world queries without surprises.

Keep this table mental model: when something fails after a migration, the cause is almost certainly one of the dialect corners above, not your core logic. Format the query first, scan for the high-risk clauses, translate them deliberately, and you turn a frustrating guessing game into a quick, mechanical fix.

Frequently Asked Questions#

What is the biggest syntax difference between PostgreSQL and MySQL? UPSERT is the biggest practical difference. PostgreSQL uses INSERT ... ON CONFLICT ... DO UPDATE with an explicit conflict target and the EXCLUDED pseudo-table, while MySQL uses INSERT ... ON DUPLICATE KEY UPDATE and infers the key. There is no clean automatic translation, so it is the clause most likely to break when porting a query.

Why does my PostgreSQL string concatenation fail in MySQL? PostgreSQL uses the || operator for string concatenation, but in MySQL || means logical OR by default. Copying a Postgres query into MySQL will not throw a syntax error; it will quietly return 0 or 1 instead of a joined string. Use the CONCAT() function in MySQL to get the same result.

Does MySQL have a real BOOLEAN type like PostgreSQL? No. PostgreSQL has a native BOOLEAN type that stores true/false. In MySQL, BOOLEAN and BOOL are aliases for TINYINT(1), which stores integers, so true becomes 1 and false becomes 0. When you port boolean columns, expect the data to surface as 0/1 and update any code that expected the string values.

Can I use INTERSECT and EXCEPT in MySQL? Only in MySQL 8.0.31 and later. PostgreSQL has supported INTERSECT and EXCEPT for a long time. On older MySQL versions you must rewrite them, using an inner join or IN subquery for INTERSECT, and a LEFT JOIN ... IS NULL or NOT EXISTS pattern for EXCEPT, watching out for NULL handling.

Is most SQL portable between PostgreSQL and MySQL? Yes. Roughly 80 percent of everyday SQL, including SELECT, JOIN, WHERE, GROUP BY, and basic INSERT, is identical or close enough to run on both engines. The differences concentrate in UPSERT, booleans, set operators, identifier quoting, string concatenation, and auto-increment, which is why a focused checklist beats reading error messages one by one.

What is the fastest way to spot dialect differences in a query? Format the query first so the structure is visible, then scan for the high-risk clauses. A formatter that supports both dialects, like the Molixa SQL formatter, indents and aligns the SQL so the ON CONFLICT, ||, and quoting differences jump out instead of hiding in a single dense line.