// advent-of-postgres/day-17
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.
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:
- A SKU in both tables gets the feed's quantity.
- A SKU that is in the feed but not in stock is added, unless it is discontinued.
- A discontinued SKU is removed from stock.
- 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.
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.
We earn commissions when you shop through the links below.
Bitrise
Managed runners for GitHub Actions
Bitrise Build Hub runs your GitHub Actions jobs on M4 Macs and AMD Linux machines. Change runs-on and keep your workflows, secrets and logs on GitHub.
Atomsized
AWS platform engineering and GitOps
Design and automation for reliable AWS and Kubernetes platforms, safer delivery workflows, and preview and UAT environments your engineers can understand and own.
DigitalOcean
Cloud infrastructure for developers
Simple, reliable cloud computing designed for developers
DevDojo
Developer community & tools
Join a community of developers sharing knowledge and tools
SMTPfast
Developer-first email API
Send transactional and marketing email through a clean REST API. Detailed logs, webhooks, and embeddable signup forms in one dashboard.
QuizAPI
Developer-first quiz platform
Build, generate, and embed quizzes with a powerful REST API. AI-powered question generation and live multiplayer.
Want to support DevOps Daily and reach thousands of developers?
Become a Sponsor