Skip to main content

// advent-of-postgres/day-17

Day 17 of 24IntermediateMERGEUpserts

The Inventory Sync

Apply the supplier's feed to the warehouse stock: update, insert, and remove what was discontinued. MERGE does it in one statement.

Opens 17 December 2026, 00:00 UTC

The toy supplier sends a feed every night, and the warehouse stock has to match it. Sugarplum usually does this by hand. Tonight you offer to do it in SQL.

The warehouse stock is in stock:

Column Type Notes
sku text Primary key
quantity int Units on shelves

The feed is in supplier_feed:

Column Type Notes
sku text Primary key
quantity int The supplier's new count
discontinued boolean The supplier no longer makes this item

The rules:

  1. A SKU in both tables gets the feed's quantity.
  2. A SKU that is in the feed but not in stock is added, unless it is discontinued.
  3. A discontinued SKU is removed from stock.
  4. A SKU that is not in the feed stays as it is.

MERGE (Postgres 15 and later) applies all of this in one statement. You can also work out the result without changing any data.

Your task

After the sync, what is the total quantity of all SKUs in stock?

Submit the number.

Stuck? Show a hint

MERGE INTO stock USING supplier_feed ON ... WHEN MATCHED AND discontinued THEN DELETE, WHEN MATCHED THEN UPDATE, WHEN NOT MATCHED AND NOT discontinued THEN INSERT. Reset today's data if you want to start over.

No database yet. You get a real Postgres database on Neon, free, with no account.
One statement at a time. Ctrl+Enter or Cmd+Enter runs it.

Download today's data as SQL to solve it on your own Postgres (14 or later).

Solved this puzzle? Share it.

Tag @thedevopsdaily and share it with the community.