Free tools Windows power users keep installed
One-click scans. No signup required.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
PostgreSQL and MySQL share much of SQL, but several important migration seams require more than a keyword swap. When porting schema migrations or application queries, check identifier quoting, upsert behavior, generated values, integer-key declarations, and affected-row assumptions. This comparison is scoped to PostgreSQL 18 and MySQL Reference Manual 26.7; verify behavior against the versions you actually deploy.
1. Identifier quoting and case rules
PostgreSQL uses double quotes to delimit identifiers. A quoted identifier preserves case and must be referenced with matching case; an unquoted identifier is folded to lower case. That can make a schema migration succeed while later application queries fail if they refer to a mixed-case name differently.
Before porting, inspect identifiers that use mixed case, reserved words, or nonstandard characters, then review every query that refers to them. PostgreSQL advises choosing a consistent practice: always quote a particular identifier or never quote it. Do not assume the target MySQL server will interpret quoting the same way without checking its configuration and version. PostgreSQL 18: lexical structure and identifiers.
2. Upsert syntax and conflict selection
The engines use different upsert clauses, and the difference is not just spelling. PostgreSQL’s INSERT ... ON CONFLICT can name a conflict target, such as a unique index or constraint; DO UPDATE requires a conflict target. MySQL uses INSERT ... ON DUPLICATE KEY UPDATE, which takes the update path when a unique index or primary key would be duplicated.
#1 Best Overall
| Engine | Clause | Conflict selection |
|---|---|---|
| PostgreSQL 18 | ON CONFLICT ... DO UPDATE |
Specify a conflict target identifying a unique index or constraint for DO UPDATE. |
| MySQL Reference Manual 26.7 | ON DUPLICATE KEY UPDATE |
A duplicate in a unique index or primary key triggers the update path; the clause does not use PostgreSQL’s explicit conflict-target form. |
Rewrite each statement for its destination engine. Decide which unique key should trigger the update and test the intended insert-versus-update behavior rather than mechanically replacing one clause with the other. PostgreSQL 18: INSERT; MySQL: INSERT … ON DUPLICATE KEY UPDATE.
3. Returning modified rows and generated values
PostgreSQL documents RETURNING for INSERT, UPDATE, DELETE, and MERGE. It can return values generated by defaults as well as other values from modified rows. MySQL’s cited generated-key guidance uses LAST_INSERT_ID() to retrieve the most recent AUTO_INCREMENT value; it is not a direct replacement for returning an entire modified row.
Rank #2
If application code consumes a row returned by a write, redesign and test that retrieval path on the destination version. Account for what the application needs: a generated key, other defaulted columns, or the complete modified row. PostgreSQL 18: returning data from modified rows; MySQL: using AUTO_INCREMENT.
4. Generated integer declarations
PostgreSQL documents serial and bigserial as autoincrementing integer types. In MySQL, the documented form attaches the AUTO_INCREMENT attribute to an integer column. Translate the declaration explicitly instead of carrying the source type name over unchanged.
As part of that rewrite, verify the key’s integer type and range, its defaults, and how application code retrieves the generated value. These references describe the forms above; they do not establish that either engine has only these identity-generation options. PostgreSQL 18: numeric types; MySQL: using AUTO_INCREMENT.
5. MySQL upsert affected-row counts
MySQL documents different affected-row values for the three outcomes of ON DUPLICATE KEY UPDATE. The connection flag CLIENT_FOUND_ROWS changes the reported value for an existing row that is set to its current values.
| MySQL upsert outcome | Documented affected-row value |
|---|---|
| A row is inserted | 1 |
| An existing row is updated | 2 |
| An existing row is set to its current values | 0; with CLIENT_FOUND_ROWS, 1 |
If application logic branches on a driver’s affected-row count, test those branches with the target server and connection settings. These figures describe MySQL’s documented behavior; they do not establish a PostgreSQL counterpart. MySQL: INSERT … ON DUPLICATE KEY UPDATE.
Recommended Free Tools
6. MySQL upserts on tables with multiple unique indexes
MySQL warns against using ON DUPLICATE KEY UPDATE on a table with multiple unique indexes: duplicate matches can result in an update of only one row. That is a risk when a migration assumes a particular key determines which row is updated. PostgreSQL’s explicit conflict-target model makes the selection mechanism different, but does not eliminate the need to check that the translated operation expresses the intended rule.
For each such table, test collisions on every unique key and confirm which row and action result. Do not rely on the clause name alone to preserve the source engine’s conflict behavior. MySQL: INSERT … ON DUPLICATE KEY UPDATE; PostgreSQL 18: INSERT.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.7. Proposed-row references and deprecated MySQL syntax
In PostgreSQL’s ON CONFLICT DO UPDATE form, excluded refers to the proposed row. MySQL’s ON DUPLICATE KEY UPDATE uses a different form. The MySQL Reference Manual marks VALUES(column) in this clause as deprecated and shows row or column aliases as the replacement pattern.
When writing or translating a MySQL upsert, use the alias form supported by the server version you deploy rather than carrying forward deprecated VALUES(column) references. For PostgreSQL, retain its own excluded reference pattern; these expressions are not interchangeable. MySQL: INSERT … ON DUPLICATE KEY UPDATE; PostgreSQL 18: INSERT.
What to check before migrating
- Inventory quoted, mixed-case, reserved-word, and unusual identifiers, then check their references in queries.
- Rewrite each upsert for the destination engine and specify or test which unique key controls its behavior.
- Replace assumptions about returned rows and generated keys with a tested retrieval path.
- Translate integer-key declarations and validate type range, defaults, and generated-value handling.
- Test application branches that inspect affected-row counts, particularly for MySQL upserts.
- For MySQL tables with multiple unique indexes, test a collision on each one.
One familiar syntax that is not a difference
LIMIT and OFFSET are used by both PostgreSQL and MySQL, so they are not among these migration differences. Check other clauses against the target engine rather than treating all familiar SQL as portable. PostgreSQL 18: SELECT.
Why syntax needs a compatibility check
The PostgreSQL 18 SQL Syntax chapter cautions: “We also advise users who are already familiar with SQL to read this chapter carefully because it contains several rules and concepts that are implemented inconsistently among SQL databases or that are specific to PostgreSQL.” The practical point for a migration is to validate both syntax and behavior on the target database, especially where application code depends on how a write selects a conflict, returns values, or reports affected rows. PostgreSQL 18: SQL syntax.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
