How do I insert multiple records with an ON CONFLICT UPDATE clause in Postgres?

Given this table, which is just an example.

CREATE TABLE orders(
     order_id text primary key,
     payment_status text not null
)

I need to do an upsert on multiple records.

INSERT INTO orders(
        order_id, 
        payment_status
)
VALUES ('101','Paid'), ('102', 'Unpaid')
ON CONFLICT (order_id) DO UPDATE SET
        payment_status = payment_status;

payment_status returns as ambiguous.

The problem I’m trying to solve here is that I have a process that imports data but if it encounters a conflict, it needs to update the current data with the new data. (it’s a historical table that sometimes gets new data for its history) I could break it up into single inserts, but that would have a dramatic performance cost.

I will need to update more than one column with this, as well, not just payment_status if that matters.

Answer

Use the excluded record:

INSERT INTO orders(
        order_id, 
        payment_status
)
VALUES ('101','Paid'), ('102', 'Unpaid')
ON CONFLICT (order_id) DO UPDATE 
   SET  payment_status = EXCLUDED.payment_status;