Upsert: Insert or Update in Python

stg_orders already holds the orders up to 05-06, one row each: 64 orders. Now 05-07's file has arrived, and loading it means adding its rows with INSERT INTO stg_orders followed by a SELECT: the query's rows go into the table next to the rows already there, and each text value from raw_orders is turned into the type of its column. But 05-07's file starts with order 5062 again, now cancelled, and stg_orders has it as paid.

The starting code gives stg_orders a PRIMARY KEY on order_id with ALTER TABLE stg_orders ADD PRIMARY KEY (order_id): the table promises one row per order. So a plain INSERT stops with a Constraint Error on 5062, and none of the 12 rows goes in, not even the 11 new orders. Without the key it would be worse: a second row for 5062, counted twice.

An upsert (update + insert) handles both cases in one statement. Add ON CONFLICT (key) at the end of the INSERT, where the key is the table's primary key: a conflict is a new row whose key is already in the table. Then DO UPDATE SET column = EXCLUDED.column says what to change in the stored row; EXCLUDED is the new row that could not go in. Rows without a conflict are simply inserted. A data engineer needs this because sources send changed records again all the time: a load that fails on them, or skips them, leaves the warehouse wrong. A price list, for example:

INSERT INTO prices
SELECT * FROM new_prices
ON CONFLICT (product)
DO UPDATE SET price = EXCLUDED.price

The check prints the number of orders and three orders from 05-07's file. The starting code prints 64 orders and 5062 as paid; your code must print:

orders
75
(1 row)
order_id | product | status
5062 | Gift card | cancelled
5065 | Lamp | paid
5075 | Gift card | cancelled
(3 rows)

Your task: in query, write an INSERT INTO stg_orders that takes every column of raw_orders for the file 'orders/2026-05-07.csv', and run it with con.execute(query): you'll see the Constraint Error. Then turn it into an upsert that updates the status of the order that is already there.

Assignment
Follow the Coding Tutorial to load 05-07's file into stg_orders with an upsert: 11 new orders added, order 5062 updated.

Hint
The Constraint Error after you run the plain INSERT is expected. ON CONFLICT (order_id) and DO UPDATE SET status = EXCLUDED.status go below the WHERE line, inside the brackets of query; each piece ends with a space.