Upsert Patterns

Definition

An upsert is the general pattern of "insert this row, or update it if it already exists" — the concept MERGE (11.6) implements in standard SQL, but which most dialects also offer through their own, often more concise, dedicated syntax.

How It Works

MySQL's dialect-specific spelling:

sql
INSERT INTO employees (id, name, department, salary)
VALUES (9, 'Farah', 'Engineering', 75000)
ON DUPLICATE KEY UPDATE salary = VALUES(salary);

PostgreSQL's dialect-specific spelling:

sql
INSERT INTO employees (id, name, department, salary)
VALUES (9, 'Farah', 'Engineering', 75000)
ON CONFLICT (id) DO UPDATE SET salary = EXCLUDED.salary;

Both achieve the identical upsert outcome as the full MERGE statement from 11.6, just with more compact, INSERT-centric syntax, and both rely on a UNIQUE/PRIMARY KEY constraint existing on the conflicting column(s) to detect "this row already exists."

Edge Cases and Pitfalls

• The exact keyword and syntax for upsert (ON DUPLICATE KEY UPDATE vs ON CONFLICT ... DO UPDATE vs full MERGE) is one of the most dialect-fragmented areas of SQL — code using one dialect's upsert syntax needs real rewriting, not just minor tweaking, to run on another engine.
• Upsert requires a UNIQUE or PRIMARY KEY constraint on the column(s) being matched — without one, the database has no defined notion of "duplicate" to trigger the update branch, and the statement behaves like a plain INSERT (or fails if uniqueness isn't otherwise guaranteed).
• VALUES(salary) (MySQL) and EXCLUDED.salary (PostgreSQL) both refer to "the value that WOULD have been inserted" — a slightly unusual but necessary way to reference the incoming row's data inside the UPDATE branch of an upsert.

Key Takeaways

• Upsert = insert-or-update, achievable via full MERGE (11.6) or a dialect-specific shortcut syntax.
• MySQL: ON DUPLICATE KEY UPDATE; PostgreSQL: ON CONFLICT ... DO UPDATE — genuinely different syntax for the same concept.
• A UNIQUE/PRIMARY KEY constraint on the matched column(s) is required for the database to detect a "duplicate" and trigger the update path.