Postgres vs MySQL Dialect Traps: The Porting Bugs Formatting Will Not Show You

Both speak "SQL" and neither speaks the other. The traps below are the ones that survive a green test suite, because they are behaviours, not syntax errors.

1. Upsert

-- Postgres
INSERT INTO t (id, views) VALUES (1, 1)
ON CONFLICT (id) DO UPDATE SET views = t.views + 1
RETURNING id, views;

-- MySQL / MariaDB
INSERT INTO t (id, views) VALUES (1, 1)
ON DUPLICATE KEY UPDATE views = views + 1;

ON CONFLICT needs a constraint or index inference to name — a unique index on a nullable column does not match ON CONFLICT (id) the way you expect, and there is no silent fallback. ON DUPLICATE KEY UPDATE fires on any unique key, so a row with two unique indexes can be updated through the one you did not intend, and LAST_INSERT_ID() reports a different thing depending on which branch ran. MySQL has no RETURNING; the idiom is LAST_INSERT_ID() plus a follow-up select, which is a race you now own.

INSERT IGNORE (MySQL) and ON CONFLICT DO NOTHING (Postgres) are not equivalent: IGNORE swallows every error class promoted to warning, including truncation and FK failures. Prefer explicit conflict targets.

2. Equality and collation

MySQL's default collations (utf8mb4_0900_ai_ci, historically utf8mb4_general_ci) are case-insensitive and often accent-insensitive: 'adam' = 'ADAM' is true, and a unique index on email therefore rejects [email protected] and [email protected] as duplicates. Postgres has no such default; citext is opt-in, and comparisons follow LC_COLLATE from initdb, which is why the same ORDER BY name sorts differently on a macOS dev box (en_US.UTF-8) and a Debian container (C.UTF-8) — in C, uppercase sorts before lowercase and Zebra precedes apple.

If a sort order is user-visible, pin it: ORDER BY name COLLATE "en-xic" is not portable; store a sort_key column you control instead.

3. Pagination

SELECT … ORDER BY id LIMIT 20 OFFSET 100000;          -- both, and both slow at depth
SELECT … WHERE id > $last ORDER BY id LIMIT 20;        -- keyset: both, index-only, constant

MySQL has no FETCH FIRST n ROWS ONLY (it wants LIMIT), no OFFSET … FETCH, no DISTINCT ON, no LATERAL before 8.0.14, and no window functions before 8.0 — a ROW_NUMBER() query written for Postgres simply errors on a managed MySQL 5.7 instance, which most "it works locally" porting tickets turn out to be.

4. Type strictness

MySQL's coercion into 0 is the dangerous column here: a stringly-typed id column from a form can turn WHERE user_id = '' into "all users with id 0". Postgres' refusal is the behaviour you want and the thing most porting pain consists of adapting to.

5. JSON

Postgres jsonb is a decoded, indexed, queryable type: ->, ->>, @>, GIN indexes, and path updates with jsonb_set. MySQL JSON is a string alias with validation plus JSON_EXTRACT/JSON_UNQUOTE/JSON_TABLE, and you cannot index a JSON column directly — you create a generated column and index that, and every consumer must remember to use it. A @> containment query has no MySQL equivalent at all; the nearest is JSON_CONTAINS, which is a function call, therefore unindexable. If the schema is genuinely document-shaped, the port changes the design, not just the syntax.

6. Dates and time zones

SELECT now();                        -- PG: timestamptz in the session zone
SELECT NOW();                        -- MySQL: DATETIME, server zone, no offset stored

Postgres TIMESTAMPTZ stores an instant and renders per session TimeZone; MySQL DATETIME stores what you wrote and TIMESTAMP stores an instant but silently converts on read using a session variable you probably never set. Add MySQL's explicit_defaults_for_timestamp = OFF default — the first TIMESTAMP column in a table auto-gets DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP — and you have a column that rewrites itself. Store UTC in a TIMESTAMPTZ/DATETIME(3), convert at the edge, and set time_zone = '+00:00' explicitly in every connection pool.

7. Sequences and identity

Postgres SERIAL/IDENTITY are sequence objects: gaps are normal, setval exists, and INSERT … RETURNING id is atomic. MySQL AUTO_INCREMENT is a table counter: a rolled-back transaction still consumes the value, and innodb_autoinc_lock_mode governs how bulk inserts reserve them. Never treat either as a business identifier for exactly that reason.

8. Transactions and DDL

Postgres DDL is transactional: BEGIN; ALTER TABLE …; ROLLBACK; is a real migration safety net, and CREATE INDEX CONCURRENTLY exists to avoid locking writes. MySQL DDL is implicit-commit — a failed multi-statement migration leaves the database half-migrated with no rollback, and every ALTER that cannot use ALGORITHM=INPLACE, LOCK=NONE locks writes for its duration. This single difference is why migration safety reads differently depending on which side of the port you are on.

Formatting notes

Dialect-aware formatters exist and none of them fix the above — they choose quoting style and function casing, not semantics. The formatter here lays every engine out with the same keyword-anchored pass; its dialect box picks only between the two spellings engines share and mean differently (a bare #, and backslashes inside quotes), so it will still happily format a query that the target database rejects; gotchas covers what it does to your text, and the query analyzer checks only the things that are dialect-independent — a missing WHERE on UPDATE/DELETE is a mistake in every one of them.