Drill 8 of 10 in Database Analyst
0

Database Analyst · INSERT … ON CONFLICT DO UPDATE · EXCLUDED

8. Upsert a supplier price feed

Concept: INSERT … ON CONFLICT DO UPDATE · EXCLUDEDshow

An upsert inserts new rows and updates existing ones in one statement: INSERT ... ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col. EXCLUDED is the incoming row that hit the conflict. It needs a unique constraint or primary key on the conflict column, and the incoming key must match it exactly, so clean it in the SELECT.

Example (different data):

INSERT INTO stock (warehouse, qty)
SELECT UPPER(warehouse), qty FROM stock_counts
ON CONFLICT (warehouse)
DO UPDATE SET qty = EXCLUDED.qty;

Your task

Every month the supplier sends a price feed, and merchandising wants the catalog brought up to date without creating duplicate products.

Upsert every price_feed row into product_catalog. Match on sku after trimming and upper-casing the feed's sku. New SKUs are inserted; existing ones get the feed's name and price, with updated_on set to feed_date. Products not in the feed stay as they are. Then return sku, name, price and updated_on from product_catalog, sorted by sku.

Starting Postgres in your browser…

Checking your access…