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…