Upsert handles an insertion conflict through a specified action. Its correctness depends on a meaningful uniqueness constraint and update policy.
Before you start
You should understand tables, keys, joins and SELECT queries. Write down a tiny dataset before reasoning about a query. SQL examples use PostgreSQL-style syntax where relevant; planner behavior, locking and available features must be checked for the database and version used in an application.
The practical goal is to reason through this situation: Insert a product by SKU and update selected fields when that SKU exists. Read the walkthrough first, then try the interview exercise before opening its answer. The important part is explaining the decision and its consequences, rather than remembering a definition alone.
Step-by-step walkthrough
Step 1: Choose domain uniqueness
SKU can define the conflict target if its scope matches the model.
Step 2: Restrict update ownership
Update only fields the caller is allowed to replace.
Step 3: Protect concurrent intent
Consider versions when stale input could overwrite newer information.
Worked scenario
Insert a product by SKU and update selected fields when that SKU exists.
INSERT INTO products (sku, display_name) VALUES ('p1', 'Widget')
ON CONFLICT (sku) DO UPDATE SET display_name = EXCLUDED.display_name;This requires a matching unique constraint. It deliberately does not update stock or pricing fields the caller does not own; upsert is not a universal merge policy.
Common mistake
Updating every column can overwrite authoritative data with stale input.
Verify the behavior
Test new SKU, existing SKU and concurrent stale updates under the intended contract.
Interview exercise
Choose a conflict target.
Answer and reasoning
Use the domain’s unique key and update only fields the caller owns, considering versions and concurrent writers.
Continue learning
Compare the scenario with the SQL and PostgreSQL interview questions and test your understanding with the SQL and PostgreSQL MCQs. For terminology and implementation details, consult the reference material.