UPSERT & MERGE
Overview
The 'Upsert' (Update or Insert) is the most critical pattern in robust backend engineering. When an API receives a payload (like a user updating their profile), the backend often doesn't know if the profile already exists. The naive approach is: 'Select to see if it exists. If yes, Update. If no, Insert.' This requires 2 network trips and causes race conditions (two clicks submit the exact same data simultaneously). UPSERT solves this at the database level by running an atomic 'Insert, but if there's a collision, Update instead' query.
Syntax
-- 1. PostgreSQL (ON CONFLICT) - The Industry Standard Upsert
INSERT INTO user_profiles (user_id, bio, theme)
VALUES (1, 'Hello World', 'Dark')
-- If the user_id (Primary Key) already exists, don't crash!
ON CONFLICT (user_id)
-- Instead, gracefully update the existing row!
DO UPDATE SET
bio = EXCLUDED.bio,
theme = EXCLUDED.theme;
-- 2. MySQL (ON DUPLICATE KEY UPDATE)
INSERT INTO user_profiles (user_id, bio, theme)
VALUES (1, 'Hello World', 'Dark')
ON DUPLICATE KEY UPDATE
bio = VALUES(bio),
theme = VALUES(theme);Common Pitfalls
- Not having a Unique/Primary Key. An Upsert physically relies on the database detecting a constraint violation (like a duplicate
user_idoremail). If you try to run an Upsert on a table without strict unique constraints, it will just blindly insert duplicates forever. - The Backend Race Condition. Implementing an Upsert manually in Node.js/Python (Checking if the row exists, then running Insert/Update) is fundamentally flawed. If two concurrent API requests hit the 'Check' at the exact same millisecond, they both think the row doesn't exist, and both try to Insert, crashing the app. ALWAYS let the Database engine handle Upserts.
Interview Questions
ON CONFLICT DO UPDATE SET bio = EXCLUDED.bio, what does the EXCLUDED keyword represent?The EXCLUDED table is a magical, temporary virtual table that holds the exact row data that you attempted to insert, but was rejected due to the conflict. This allows you to dynamically map the rejected data into the Update clause.
Real-World Example
Recording daily page views for a blog post. If the row for today doesn't exist, create it. If it does exist, increment the counter natively.
INSERT INTO daily_page_views (post_id, view_date, total_views)
VALUES (42, CURRENT_DATE, 1)
ON CONFLICT (post_id, view_date)
DO UPDATE SET
total_views = daily_page_views.total_views + 1;Check Your Knowledge
Test your understanding of UPSERT & MERGE with these quick questions.