4. Upserts and conditional writes

Description

By the end of this lesson you'll be able to turn a write that duplicates —a blind INSERT that creates a new row every time— into a write that doesn't duplicate, using an upsert: "insert if it doesn't exist, update if it does, but never duplicate." You're going to understand the piece without which the upsert doesn't work —the uniqueness constraint on the key column—, you're going to write an upsert in SQL with ON CONFLICT, and you're going to know how the database node and the spreadsheet node express it inside n8n.

This matters because the upsert is, by far, the idempotency tool you're going to use the most in your real life with workflows. Lesson 5's header depends on the API cooperating; the upsert doesn't depend on anyone: it's your database guaranteeing you that an order_id can't exist twice. Every time a workflow writes to a table, a sheet, or a CRM you control, the upsert is the answer to the duplicate. It's the one that fixes the INSERT that today gives Cumbre a second row for every webhook retry.

Connection to the module: lesson 3 gave you the key —the natural order_id or the synthetic hash—. This lesson uses it: the key is the column the upsert uses to decide "this already exists." It's the first of the two ways to apply a key to an effect; lesson 5 is the other (the header, for third-party APIs). Lesson 6 is going to show why the upsert is superior to the naive "check then insert" solution —because the upsert is atomic and the other isn't—. And lesson 8's project turns, among other things, the CRM's INSERT into the upsert you learn here.

The problem: the blind INSERT

Let's go back to the effect in Cumbre's HTTP Request that writes to the CRM. Suppose the CRM stores orders in an orders table, and the workflow inserts them like this:

INSERT INTO orders (order_id, customer_id, amount, status)
VALUES ('ORD-2041', 'CUST-118', 1780, 'pending');

This INSERT is a textbook "add to cart": every time it runs, it creates a new row. Lesson 2 already classified it as not idempotent, and now we see the concrete damage. When the webhook arrives twice with ORD-2041, this INSERT runs twice, and the orders table ends up with two rows for the same order:

Now any report that counts orders gives an inflated number, any process that scans the table processes ORD-2041 twice, and if another workflow reads "order ORD-2041" it doesn't know which of the two rows is the right one. A single duplicate dirties everything downstream that touches that table.

What we want is for the second write to recognize "this order_id is already here" and not create a new row. We want to turn the table into a set by order_id —remember lesson 2's set, which ignores duplicates?—. That conversion is called an upsert.

What an upsert is

Upsert is a fusion of two words: update + insert. It describes a single operation that decides on its own which of the two to do:

If the row doesn't exist, insert it. If it already exists, update it (or do nothing). Never create a duplicate.

Think of your phone's contact list. When you save "Juan" with his number, your phone doesn't create a second "Juan" every time you update his number: it checks whether a contact named Juan already exists and, if it finds one, updates his data instead of duplicating him. Your contact list doesn't have five "Juan"s; it has one, with the most recent information. That's an upsert: the operation is "let Juan exist with this data," not "add a Juan." Setting a state, not accumulating —exactly lesson 2's idempotent heuristic—.

For this to work, the operation needs to know which field to use to decide whether something "already exists." In the contact list it's the name. At Cumbre it's order_id. That field is lesson 3's key put to work: the upsert uses it to ask "is there already a row with this order_id?"

The invisible piece without which nothing works: the uniqueness constraint

Here's the detail most people overlook, and why their upsert "doesn't duplicate in testing but duplicates in production." Pay attention, because it's half of this lesson.

For the database to be able to decide "this row already exists," there has to be a rule telling it which column can't repeat. That rule is called a uniqueness constraint (unique constraint) or a unique index, and it's defined on the table, not in your workflow. At Cumbre, on the order_id column:

-- This is done ONCE, when creating or preparing the table.
-- It tells the database: "there cannot be two rows with the same order_id".
ALTER TABLE orders ADD CONSTRAINT orders_order_id_unique UNIQUE (order_id);

Without this constraint, the database has no way of knowing what "already exists" means, and the upsert has nothing to collide against. Worse: in many cases, an upsert on a column with no uniqueness constraint silently degrades into a plain INSERT, and you're back to duplicating with no error to warn you.

The analogy: the upsert is the instruction "don't duplicate Juan," but the uniqueness constraint is what defines what counts as "the same Juan" —the name? the phone number? the email?—. Without deciding that first, the instruction makes no sense. The uniqueness constraint is that decision, made once, at the table level.

A hard rule that saves you hours: before writing any upsert, confirm there's a uniqueness constraint on the key column. If there isn't one, create it. It's step zero, and skipping it is the number-one cause of "my upsert doesn't work."

The upsert in SQL: ON CONFLICT

Let's see a real upsert. In PostgreSQL —the database the Starter Kit v2 you use in the labs brings— the upsert is written with the ON CONFLICT clause:

INSERT INTO orders (order_id, customer_id, amount, status)
VALUES ('ORD-2041', 'CUST-118', 1780, 'pending')
ON CONFLICT (order_id) DO NOTHING;

Read it out loud, because it reads almost like plain English: "insert this row; and if there's a conflict on order_id —that is, if a row with that order_id already exists— do nothing." The first time ORD-2041 arrives, there's no conflict: it inserts. The second time, it collides with the uniqueness constraint on order_id, DO NOTHING kicks in, and no second row gets created. The table ends up with a single row, no matter how many times it runs.

There are two flavors of ON CONFLICT, and the difference matters:

DO NOTHING — if it already exists, ignore the write entirely. The row that was already there stays. This is what you want when the first write is the truth and the repeats bring nothing new.

DO UPDATE — if it already exists, update the row with the new data. Used when the repeat can bring fresher information you want to keep:

INSERT INTO orders (order_id, customer_id, amount, status)
VALUES ('ORD-2041', 'CUST-118', 1780, 'pending')
ON CONFLICT (order_id) DO UPDATE
  SET amount = EXCLUDED.amount,
      status = EXCLUDED.status;

EXCLUDED is a special PostgreSQL word meaning "the values you were trying to insert." So this says: "if ORD-2041 already exists, update its amount and status with the ones this arrival carried." Notice it's still idempotent: updating ORD-2041 to the same amount and status ten times leaves it the same as doing it once. Setting a value, not accumulating.

Which of the two should you choose? If the two arrivals of the same event carry identical data —the typical retry case—, DO NOTHING and DO UPDATE give the same result, and DO NOTHING is simpler. If the second arrival could carry a correction (a more advanced status, a piece of data that was missing), DO UPDATE keeps the most recent one. For Cumbre's pure retry case, DO NOTHING is more than enough.

A note for those using MySQL instead of PostgreSQL: the idea is identical, the syntax changes. MySQL writes it as INSERT ... ON DUPLICATE KEY UPDATE ..., and it also depends on a unique key existing on the column. The concept travels across databases; check your own's exact syntax in its documentation.

Worked example: order-triage's upsert in n8n

Let's do it in n8n, on Cumbre's orders table, and prove that re-running leaves a single row.

Step 0 — The uniqueness constraint (once). Before anything, make sure the table has the constraint on order_id. In a Postgres node in "execute a query" mode, or directly in your database client, run once:

ALTER TABLE orders ADD CONSTRAINT orders_order_id_unique UNIQUE (order_id);

What to expect: if the table didn't have the constraint, it's created with no fuss. If it already had it, you'll get an "already exists" error you can ignore. If running it tells you there are duplicate values, good news in disguise as bad news!: it means you already have old duplicates, and you have to clean them up before you can enforce uniqueness.

Step 1 — The database node. In n8n, the Postgres node offers different operations. One of them is specifically an upsert —in the interface it usually appears as an operation of type Upsert or "Insert or Update"; check the exact label in your version, because the name has varied—. That operation asks you for two things: the column to detect the conflict on (here, order_id) and the fields to write. Underneath, it does exactly the ON CONFLICT you saw above.

If you prefer full control and don't want to depend on the node's label, use the execute a query operation (Execute Query) and write the INSERT ... ON CONFLICT by hand, passing the item's values as parameters. It's more explicit and works the same in any version:

-- In a Postgres node, "Execute Query" operation.
-- Values come from the item; use the node parameterization, not text concatenation.
INSERT INTO orders (order_id, customer_id, amount, status)
VALUES ($1, $2, $3, 'pending')
ON CONFLICT (order_id) DO NOTHING;

$1, $2, $3 are parameter placeholders the node fills with the order's fields (order_id, customer_id, amount). Passing the values as parameters, instead of pasting them into the query text, is the correct, safe way to do it; the ecosystem's APIs guide explains why.

Step 2 — Run it once. Trigger the workflow with order ORD-2041. What to expect: the orders table now has a row with ORD-2041. Query it with SELECT * FROM orders WHERE order_id = 'ORD-2041' and you'll see exactly one row.

Step 3 — Run it again, on purpose. Trigger the same order ORD-2041 again, simulating the webhook's retry. What to expect: the node runs with no error —this matters, it doesn't fail— and when you query SELECT * FROM orders WHERE order_id = 'ORD-2041' again there's still exactly one row. The second execution collided with the uniqueness constraint, DO NOTHING absorbed it, and it didn't duplicate.

That's lesson 8's idempotency test in miniature: not "it ran with no error" (which is also true), but "I ran it twice and there's exactly one row." You just turned a non-idempotent effect into an idempotent one without depending on anyone but your own database.

The upsert in a spreadsheet: Google Sheets

Not every workflow writes to a real database. Many small teams —like Cumbre very well might— store data in a Google Sheets spreadsheet. The good news: the same upsert idea exists there.

n8n's Google Sheets node offers an operation that does precisely this: instead of just "append a row" (which would duplicate), it has an operation of type Append or Update —which checks the existing row values(s) and decides. In the interface, that operation asks you for a column to match on: you choose order_id, and the node looks for whether there's already a row with that order_id. If it finds one, it updates it; if not, it appends a new one. Check the exact label for the operation and the field in your version, because n8n tweaks those names from time to time.

The important difference against the database: a spreadsheet doesn't have a real uniqueness constraint. Nothing at the file level stops two rows from existing with the same order_id; uniqueness is guaranteed by the node's search-and-decide, not by the sheet. That has two consequences worth being clear about. First, if two rows with the same order_id slipped in through any path (for example, someone pasted them by hand, or an old workflow used "append" instead of "append or update"), the matching operation can get confused about which one to update. Second, and more delicate, this strategy has a concurrency weakness the database doesn't have —and it's exactly lesson 6's topic—: if two executions look up "does ORD-2041 exist?" at the same time, both can see it doesn't and both append a row. The database, with its uniqueness constraint, closes that window; the spreadsheet doesn't, entirely. That's why, when correctness truly matters —money, inventory—, a database with a uniqueness constraint is more robust than a sheet.

That said, for many real cases the sheet with "append or update" is perfectly sufficient, and it's infinitely better than "blindly append." Use it knowing its limit.

A practical rule for deciding between a sheet and a database: if the data you're writing is money, inventory, or anything whose duplicate costs someone something —a charge, a stock reservation, a commission—, go with a database with a uniqueness constraint, which closes the concurrency window at the root. If the data is informational and an occasional duplicate can be cleaned up with no consequences —a log row for internal reference—, the sheet with "append or update" is a pragmatic, sufficient choice. It isn't that one is "good" and the other "bad"; it's that the robustness you need depends on how expensive it is to get it wrong, and choosing with that criterion is part of the craft.

Why the upsert is ONE single operation (and why that matters)

There's a virtue of the upsert that looks small and is enormous, and that lesson 6 is going to turn into the heart of its argument. It's worth planting it here.

The upsert is a single atomic instruction. "Atomic" means the database runs it as an indivisible block: the decision "does it already exist?" and the action "insert or ignore" happen glued together, with nothing able to slip in between. Nobody can sneak in between the "does it exist?" and the "insert" to change the answer.

Compare this with the naive alternative everyone thinks of first: "first I do a SELECT to see if ORD-2041 already exists, and if it doesn't, I do an INSERT." Those are two separate operations, with a gap between them. And another execution fits in that gap. Imagine two webhook retries running almost at the same time:

Execution A: SELECT ORD-2041 → does not exist
Execution B: SELECT ORD-2041 → does not exist   (still does not exist, A has not inserted yet!)
Execution A: INSERT ORD-2041 → creates the row
Execution B: INSERT ORD-2041 → creates ANOTHER row

Both asked "does it exist?", both saw "no," and both inserted. Two rows. The check served no purpose because time passed between asking and acting, and in that time the world changed.

The upsert has no such gap. The uniqueness constraint plus ON CONFLICT make the decision and the action the same indivisible operation: when B tries to insert, the database already has A's row and DO NOTHING absorbs it, no matter how close together they ran. That's why the upsert is robust where "check then insert" is fragile.

Don't worry about mastering this yet —lesson 6 devotes its full attention to it, with the technical name and everything—. For now, hold on to a rule: always prefer a single atomic operation (the upsert) over two separate operations (check then create). It's the difference between an idempotency that holds up under concurrency and one that breaks the moment two things run at once.

Which column to use as the upsert's key

A practical wrap-up before the common mistakes: which column do you upsert on, order_id or lesson 3's idempotency_key?

The answer follows lesson 3's same logic. If the event has a good natural key —the stable order_id of web orders— use it as the conflict column: it's readable, and when you debug a duplicate in production you'll appreciate being able to search for ORD-2041 instead of a hash. If the event doesn't have a natural key —WhatsApp orders with no order_id— store the synthetic idempotency_key in a table column and put the uniqueness constraint on that column. The upsert then collides on idempotency_key.

In many real designs it's worth having both: the business column (order_id when it exists) and an idempotency_key column that's always populated —with the natural key when there is one, or with the hash when there isn't—, and putting the uniqueness constraint on idempotency_key. That way your upsert always has a single column to collide against, no matter the channel. It's a pattern module 4 formalizes when you build the deduplication ledger; for now hold on to the idea that the conflict column is your idempotency key, natural or synthetic.

Common mistakes

Doing the upsert with no uniqueness constraint (practical). What happens: someone configures the node with the upsert operation, chooses order_id as the matching column, tests it, and duplicate rows still show up. Why it happens: the table has no uniqueness constraint on order_id, so the database has nothing to "collide" against. Depending on the node and version, the upsert can degrade into a plain INSERT, or the node can do a "search and decide" in two steps that suffers lesson 6's race condition. Either way, it duplicates. How to spot it: query the table's constraints —in PostgreSQL, check orders's indexes and constraints— and confirm there's a UNIQUE on the key column. If it's not there, that's the problem, not your node configuration. How to fix it: create the constraint once with ALTER TABLE ... ADD CONSTRAINT ... UNIQUE (order_id). If creating it fails because of existing duplicate values, clean those up first and then enforce uniqueness. Step zero of every upsert is guaranteeing the constraint.

Choosing the wrong conflict column (practical). What happens: the constraint and the upsert get put on a column that doesn't uniquely identify the event —for example customer_id instead of order_id—. The result is worse than duplicating: now two different orders from the same customer collide, and the second order overwrites or discards the first. You just lost a sale. Why it happens: "a column that repeats little" gets confused with "the column that identifies the event." customer_id legitimately repeats —a customer places many orders—; it isn't an idempotency key. How to spot it: ask yourself whether two different events could have the same value in that column. If yes (two orders from the same customer share customer_id), the column is wrong. How to fix it: the conflict column must be lesson 3's idempotency key —order_id or idempotency_key—, that is, something identical for the same event and different for different events. Neither too coarse nor too fine.

Using DO UPDATE when the second arrival carries worse data (practical). What happens: ON CONFLICT DO UPDATE gets chosen to "keep the most recent," but it turns out the retry's second arrival sometimes carries incomplete data that overwrites good data from the first —for example, a status that regresses from paid back to pending—. Why it happens: DO UPDATE trusts that the most recent arrival is the best one, and in a retry that isn't always true; the retry can be an old copy of the event. How to spot it: check whether the field you're updating can "get worse" between arrivals; status fields that should only advance are the suspects. How to fix it: for pure retries of identical data, prefer DO NOTHING —the first write is the truth, period—. If you need DO UPDATE but the state should only advance, add a condition preventing regression (in PostgreSQL, a WHERE clause in DO UPDATE that only updates if the new state is "greater"). The point is not to blindly overwrite.

Exercises

Exercise 1 — Read the upsert. Explain in your own words what each of these two queries does when ORD-2041 already exists in the table, and how they differ:

-- Query A
INSERT INTO orders (order_id, amount, status)
VALUES ('ORD-2041', 1780, 'pending')
ON CONFLICT (order_id) DO NOTHING;

-- Query B
INSERT INTO orders (order_id, amount, status)
VALUES ('ORD-2041', 1780, 'paid')
ON CONFLICT (order_id) DO UPDATE
  SET status = EXCLUDED.status;

Exercise 2 — Diagnose the upsert that duplicates. A coworker swears they configured the upsert correctly —chose the upsert operation, set order_id as the matching column— but the table keeps filling up with duplicate ORD-2041s on every retry. The node throws no error. What's the most likely cause, and how do you confirm it?

Exercise 3 — Choose the conflict column for the three channels. Cumbre stores every order in an orders table, regardless of channel. Design the conflict-column strategy that works for all three, using what you learned in lessons 3 and 4:

• web: carries a stable order_id.
• whatsapp: no order_id, but you compute a synthetic idempotency_key for it.
• rep_csv: no order_id, also with a synthetic idempotency_key.

ALTER TABLE orders ADD CONSTRAINT orders_idem_unique UNIQUE (idempotency_key);

INSERT INTO orders (idempotency_key, order_id, customer_id, amount, status)
VALUES ($1, $2, $3, $4, 'pending')
ON CONFLICT (idempotency_key) DO NOTHING;

Summary and next step

In this lesson you turned the blind INSERT —which gives Cumbre a new row for every webhook retry— into an upsert: "insert if it doesn't exist, update or ignore if it does, never duplicate." You understood it with the contact list that doesn't create two "Juan"s, you saw its SQL form with ON CONFLICT (order_id) DO NOTHING and its DO UPDATE variant, and you learned the invisible piece without which nothing works: the uniqueness constraint on the key column, which is what actually prevents the duplicate and whose absence is the number-one cause of "my upsert doesn't protect." You took it to n8n —the Postgres node's upsert operation or an Execute Query with ON CONFLICT, and Google Sheets's "append or update" operation with its matching column— and you tested real idempotency: running twice and finding exactly one row. And you closed by choosing the conflict column: the natural order_id when it exists, the synthetic idempotency_key when it doesn't, unified into a single column that's always populated.

Before moving on to lesson 5 you should be able to: write an upsert with ON CONFLICT; explain why, without the uniqueness constraint, the upsert degrades into an insert that duplicates; and choose the correct conflict column for an event with and without a natural key.

The upsert solves the duplicate when the effect is your own database, where you control the uniqueness constraint. But Cumbre's other effect —the charge at the payment gateway— lives in a third-party system, where you can't create a uniqueness constraint. Lesson 5 solves that case: the Idempotency-Key header, the way serious APIs let you say "this charge and the previous one are the same" without you controlling their database. And for APIs that don't offer that header, you're going to see the check-before-create pattern... with a big warning, because that pattern hides the trap lesson 6 is going to take apart.

Resources

• INSERT ... ON CONFLICT — PostgreSQL documentation — PostgreSQL's official upsert reference: DO NOTHING, DO UPDATE, EXCLUDED, and the unique-index requirement. This lesson's foundation.
• Postgres node — n8n Docs — the Postgres node's operations in n8n, including the upsert one and the execute-query one; check there the exact label for each operation in your version.
• Google Sheets node — n8n Docs — the "append or update" operation and its matching column, the spreadsheet world's upsert.
• UNIQUE constraints — PostgreSQL documentation — how the uniqueness constraint that makes the upsert possible is defined; step zero you should never skip.
• INSERT ... ON DUPLICATE KEY UPDATE — MySQL documentation — MySQL's equivalent of the upsert, in case your database isn't PostgreSQL; same idea, different syntax.