PostgreSQL vs MySQL Syntax: Every Difference That Breaks a Migration

Side-by-Side Table

The rows below are the ones I see break code in real migrations — not every difference, just the ones that change results or throw errors.

Task	PostgreSQL	MySQL 8
String concat	a || b || c	CONCAT(a, b, c) — || is OR by default
Current timestamp	NOW() / CURRENT_TIMESTAMP	NOW() (same name, warning-free)
Truncate to month	DATE_TRUNC('month', d)	DATE_FORMAT(d, '%Y-%m-01')
Add interval	d + INTERVAL '7 days'	DATE_ADD(d, INTERVAL 7 DAY)
Cast	val::int or CAST(val AS integer)	CAST(val AS SIGNED) / CONVERT(val, SIGNED)
Row limiting	LIMIT 10 OFFSET 20	LIMIT 20, 10 — offset first, count second
Upsert	INSERT ... ON CONFLICT (id) DO UPDATE SET x = EXCLUDED.x	INSERT ... ON DUPLICATE KEY UPDATE x = new.x (alias syntax, 8.0.20+)
Auto-increment id	GENERATED ALWAYS AS IDENTITY (or legacy SERIAL)	AUTO_INCREMENT
Boolean column	Native BOOLEAN — true/false	TINYINT(1) — stores 1/0
JSON field access	data->>'name' (text), data->'tags' (json)	data->>'$.name', path syntax mandatory
Regex match	col ~ '^a', case-insensitive col ~* '^a'	col REGEXP '^a' (case-insensitive by default)
Regex replace	REGEXP_REPLACE(col, p, r, 'g')	REGEXP_REPLACE(col, p, r) — replaces all by default
Group concat	STRING_AGG(col, ',')	GROUP_CONCAT(col SEPARATOR ',')
Identifier quoting	"col" — double quotes	`col` — backticks
NULL sort order (ASC)	NULLs last	NULLs first
Return inserted row	INSERT ... RETURNING *	No RETURNING — read LAST_INSERT_ID() after
FULL OUTER JOIN	Supported	Not supported — emulate with LEFT/RIGHT UNION
Window functions	Since 8.4	Since 8.0 — older MySQL silently can't parse them

1. String Concatenation Is the #1 Migration Bug

MySQL treats || as logical OR by default, so ported code like first_name || ' ' || last_name silently returns 0 (both operands evaluate truthy, OR returns 1, and then implicit casting turns it into 0 or 1). It does not error — it just returns wrong data. If you cannot change the SQL, enable the mode: SET sql_mode = CONCAT(@@sql_mode, ',PIPES_AS_CONCAT'); — but changing the SQL to CONCAT() is the honest fix.

2. Interval Math vs Date Functions

PostgreSQL treats intervals as values you can add: WHERE created_at > NOW() - INTERVAL '30 days'. MySQL has no interval arithmetic — everything is a function call: WHERE created_at > DATE_SUB(NOW(), INTERVAL 30 DAY). The upside of MySQL's model is readability with dynamic units via the EXTRACT-style short forms; the downside is that expressions like "first day of last month" become a DATE_FORMAT ritual. Full function atlas: SQL date functions cheat sheet.

3. Upsert Semantics Differ More Than the Syntax

PostgreSQL requires an explicit conflict target and only fires on that constraint: ON CONFLICT (email) DO UPDATE SET last_login = EXCLUDED.last_login. MySQL fires on any unique-key collision — if your table has two unique indexes, a row that collides on the secondary index takes the UPDATE path too, which you may not have intended. MySQL 8.0.20+ deprecated the VALUES() function inside the update clause; write INSERT INTO t (id, x) VALUES (1, 2) AS new ON DUPLICATE KEY UPDATE x = new.x.

4. Strictness: Silent Coercion vs Hard Errors

PostgreSQL refuses WHERE id = '123abc' on an integer column (invalid input syntax). MySQL coerces: a non-numeric string becomes 0 with a warning, so WHERE id = '123abc' quietly matches id 0. This one difference explains a whole genre of "it worked in dev" incidents — always pass properly typed parameters. In the other direction, MySQL's ONLY_FULL_GROUP_BY (on by default since 5.7) rejects bare columns like PostgreSQL does, so most GROUP BY code now ports cleanly.

5. LIMIT Argument Order

Both engines accept LIMIT count OFFSET offset, but MySQL additionally accepts the compact LIMIT offset, count. Copying a MySQL paging query into PostgreSQL with the compact form produces a syntax error — which is at least loud. The dangerous direction is the reverse: LIMIT 20, 10 means "skip 20, return 10" and there is no PG equivalent to confuse it with. Prefer the explicit OFFSET keyword in shared code.

6. Identifier Quoting and Case

Unquoted identifiers fold to lowercase in PostgreSQL and to the filesystem case in MySQL (on Linux, table names are case-sensitive by default; on macOS they are not). Quoted identifiers in PG are case-sensitive forever after; backticked identifiers in MySQL preserve case but match case-insensitively on macOS. The practical rule: never rely on mixed-case names, and when migrating DDL, run every CREATE TABLE through a linter that flags double-quoted identifiers — they do not mean what they look like in MySQL.

Migration Checklist

• grep for || in string expressions — replace with CONCAT() for MySQL.
• grep for DATE_TRUNC — rewrite with DATE_FORMAT on MySQL, or vice versa.
• grep for LIMIT n, m twice — once for MySQL compact form, once for PG compatibility.
• Check every upsert: does it have exactly one unique key, or does MySQL's broader trigger matter?
• Run EXPLAIN on the 10 most frequent queries in both engines — NULL sort order and index behavior shift plans.
• Confirm sql_mode on target: ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES should be on for parity with PostgreSQL behavior.

Try It Yourself

Paste a MySQL query into our SQL generator and ask for "the PostgreSQL version" — it rewrites the syntax differences above and flags the semantic ones it cannot fix automatically.