SQL practice: Upserts: INSERT … ON CONFLICT

Try to insert a product with product_id 1 called Duplicate Sheet, in category ACP Sheet, unit_price 1000 and cost_price 800 — but skip it silently if that id already exists.

Insert product 1 again, this time updating its unit_price to 2999 if the id already exists. Leave every other column of the existing row alone.

Insert a genuinely new product with product_id 20, named Corner Bead 2m, category Accessory, unit_price 310 and cost_price 200, using ON CONFLICT DO NOTHING so it is safe to re-run.

Set product 2 to a unit_price of 2500, but only if that is higher than its current price. Use an upsert with a conditional DO UPDATE.

Raise the price of product 3 to 3500 with an upsert, and return the product_id and unit_price of whatever was written.

In an upsert, what does EXCLUDED refer to?

Use the MERGE statement to update products (aliased as p) using an inline dataset (SELECT 1 AS product_id, 3200.00::numeric AS unit_price) aliased as s, matching on product_id. When matched, update p.unit_price with s.unit_price.