Why should you care?

PostgresVerse nightly inventory sync from warehouse file merges stock counts without separate UPDATE and INSERT scripts.

See it live — copy this example

Run in pgAdmin or psql.

MERGE INTO products AS t
USING staging_products AS s
ON t.product_id = s.product_id
WHEN MATCHED AND s.stock_qty IS DISTINCT FROM t.stock_qty THEN
  UPDATE SET stock_qty = s.stock_qty, updated_at = now()
WHEN NOT MATCHED THEN
  INSERT (product_id, name, stock_qty) VALUES (s.product_id, s.name, s.stock_qty);

What happened?

• staging_products drives merge.
• Matched rows update only if stock changed.
• Unmatched rows insert new products.
• One atomic statement.

Practice next

1. Create staging_products with mix of existing and new ids.
2. Run MERGE and compare products table.
3. Add WHEN NOT MATCHED BY SOURCE DELETE for full sync (careful).
4. Add AND s.deleted THEN DELETE branch for soft-delete sync.
5. Compare row counts with INSERT ON CONFLICT DO UPDATE.

Remember

MERGE combines insert/update (and optional delete). IS DISTINCT FROM avoids noop updates. Requires PostgreSQL 15+.

PostgresVerse WMS sync

Warehouse management MERGE refreshes 50k SKUs every 15 minutes.

Outcome: Inventory accuracy improves; script lines cut in half.