Both engines speak SQL, and for the majority of statements, plain SELECTs, joins, simple inserts, the port is a no-op. That is precisely what makes the differences dangerous: the porting work is not evenly distributed, so teams sail through the first 80% of queries and then lose weeks to the remainder, or worse, ship queries that run on both engines but mean different things.
Having operated MySQL and now living mostly in PostgreSQL, I want to be fair here: most of these differences are not MySQL being wrong, they are two engines with different histories and defaults. But if you are moving from MySQL 8.x to PostgreSQL 16/17, this is the delta that actually bites, roughly in the order it will bite you.
Quoting: backticks, double quotes, and case folding
MySQL quotes identifiers with backticks: `order`, `user`. PostgreSQL follows the SQL standard and uses double quotes: "order", "user". Mechanical enough, but two second-order effects matter more than the character swap.
First, in default MySQL, double quotes delimit strings, so WHERE status = "active" is valid MySQL and an error, or worse, a column reference, in PostgreSQL. Sweep the codebase for double-quoted string literals and convert them to single quotes; enabling the ANSI_QUOTES sql_mode on MySQL ahead of the migration makes MySQL enforce the standard behavior too, which flushes these out while you still have one engine.
Second, case folding. PostgreSQL folds unquoted identifiers to lowercase, so CustomerOrders and customerorders are the same table, but "CustomerOrders" quoted is a different one. MySQL table-name case sensitivity depends on the filesystem and lower_case_table_names. If your MySQL schema has mixed-case table names, decide now: rename everything to snake_case during migration (my strong recommendation) or commit to quoting every identifier forever. The first option costs a week; the second costs a little sanity indefinitely.
Upserts: INSERT IGNORE and ON DUPLICATE KEY UPDATE
This is the biggest semantic minefield. MySQL's INSERT IGNORE looks like PostgreSQL's ON CONFLICT DO NOTHING, but it is broader: IGNORE downgrades a whole class of errors to warnings, duplicate keys, but also some invalid values and constraint issues. ON CONFLICT DO NOTHING handles exactly unique and exclusion constraint conflicts and nothing else. If code was leaning on IGNORE to swallow bad data, the PostgreSQL port will surface those rows as errors, which is usually an improvement, but it is a behavior change to plan for, not discover.
ON DUPLICATE KEY UPDATE maps to ON CONFLICT DO UPDATE, with one structural difference: MySQL reacts to a violation of any unique key, while PostgreSQL makes you name the conflict target, the specific columns or constraint. On tables with multiple unique constraints, that forces a decision MySQL let you avoid, and the decision is worth making consciously.
-- MySQL
INSERT INTO page_views (page_id, day, views)
VALUES (42, '2026-07-01', 1)
ON DUPLICATE KEY UPDATE views = views + VALUES(views);
-- PostgreSQL
INSERT INTO page_views (page_id, day, views)
VALUES (42, '2026-07-01', 1)
ON CONFLICT (page_id, day)
DO UPDATE SET views = page_views.views + EXCLUDED.views;EXCLUDED plays the role of MySQL's VALUES() function (itself deprecated in MySQL 8.0.20 in favor of row aliases). Also note REPLACE INTO has no direct PostgreSQL equivalent, and its MySQL semantics are delete-then-insert, which fires delete triggers and cascades foreign keys; most REPLACE INTO call sites actually want ON CONFLICT DO UPDATE, which is an update, not a delete. Check each one.
LIMIT syntax and the comma trap
PostgreSQL supports LIMIT n OFFSET m. MySQL supports that too, but a lot of legacy code uses the comma form, LIMIT 10, 20, and the comma form means offset 10, count 20, the offset comes first. Mechanical translators that map LIMIT a, b to LIMIT a OFFSET b silently swap page size and page position, and the bug this creates, pagination that looks plausible but skips and repeats rows, can survive code review easily. Grep for the comma form specifically and translate it by hand.
GROUP BY strictness
Historically MySQL allowed selecting non-aggregated columns not in the GROUP BY and returned an arbitrary row's value for them. Since 5.7, ONLY_FULL_GROUP_BY is on by default and MySQL 8.x mostly matches PostgreSQL's strictness, so if your codebase runs clean on a default-configured MySQL 8, this section is nearly free. The trap is fleets that disabled ONLY_FULL_GROUP_BY to keep legacy queries alive: every one of those queries is a porting task, and each carries a buried question, which row did we actually want here? PostgreSQL also honors functional dependence on a grouped primary key, so GROUP BY users.id while selecting users.name is legal in both engines. The queries that break are the genuinely ambiguous ones, and they were returning arbitrary data all along; the port is where you finally decide what they meant.
String and date functions: mostly renames, two real traps
Much of this layer is mechanical renaming: IFNULL becomes COALESCE (already portable), SUBSTRING_INDEX becomes split_part, GROUP_CONCAT becomes string_agg (both support ORDER BY inside the aggregate), DATE_FORMAT becomes to_char with a different format vocabulary, UNIX_TIMESTAMP becomes extract(epoch from ...), IF(x, a, b) becomes CASE WHEN. Tedious, greppable, low-risk.
Two differences are semantic, not cosmetic. First, NULL in concatenation: MySQL's CONCAT returns NULL if any argument is NULL. PostgreSQL's || operator does the same, but the concat() function ignores NULLs and concatenates the rest. Code ported blindly from CONCAT to concat() changes behavior on NULL inputs, and code ported to || keeps MySQL behavior. Choose per call site.
Second, comparison case sensitivity. MySQL's default collations are case-insensitive, so WHERE email = 'Bob@Example.com' matches bob@example.com. PostgreSQL comparisons are case-sensitive by default. Every string equality in the application is a candidate behavior change. The clean fixes are lower() on both sides backed by an expression index, or the citext type for columns that are semantically case-insensitive, like emails. This one deserves a dedicated audit, because it fails silently: no errors, just queries that stop matching rows they used to match.
Integer division and implicit casts
The quietest trap in the list. In MySQL, 5 / 2 returns 2.5, the division operator produces a decimal. In PostgreSQL, integer / integer is integer division: 5 / 2 = 2. Any ported arithmetic on integer columns, revenue / count, progress percentages, averages built by hand, silently loses the fractional part. MySQL's integer division operator is DIV, which almost nobody used, so assume every / between integers in ported SQL needs a look and an explicit cast:
-- Meaning preserved from MySQL: cast before dividing
SELECT sum(amount_cents)::numeric / count(*) AS avg_cents
FROM payments
WHERE created_at >= now() - interval '30 days'
AND amount_cents <> 0;Related: MySQL coerces types enthusiastically in comparisons, '5abc' equals 5, the string '1' matches the integer 1 everywhere. PostgreSQL is far stricter and raises errors where MySQL guessed. During porting this feels like friction; in operation it is protection. Budget time for the errors to surface in staging, and resist the urge to paper over them with casts sprinkled at random, each one is telling you about a real type mismatch in the schema or the application.
How to actually run the port
Grep-driven porting works because nearly everything above has a searchable signature: backticks, double-quoted strings, INSERT IGNORE, ON DUPLICATE, REPLACE INTO, the LIMIT comma form, GROUP_CONCAT, DATE_FORMAT, bare / between integer columns. Build the inventory first, count call sites per pattern, and you have an honest estimate instead of a vibe. Then port with tests against a real PostgreSQL, not a compatibility shim, and stage the riskiest categories, upserts and case sensitivity, through extra review. The migration review checklist covers the sign-off gates this feeds into.
After the port: MonPG watches the queries you just rewrote
Every rewritten query is a small hypothesis: same rows, acceptable plan, no regression. Some of those hypotheses are wrong, and you want to find out from monitoring rather than from users. The first weeks after cutover are when a ported query with a missing index or an accidentally changed predicate shows its true cost under production traffic.
MonPG monitors PostgreSQL only, and it earns its place here by baselining the new workload from day one: pg_stat_statements history per query family, wait events, lock chains, and index usage that flags which ported queries are scanning instead of seeking. When one of the rewrites regresses, you get the before-and-after shape instead of an anecdote. The PostgreSQL monitoring guide describes the baseline worth standing up before the ported SQL meets real traffic.