Upserts
On This Lesson 4 topics
This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.
In this lesson
insert or update in one atomic statement.
Planned topics
INSERT ... ON CONFLICT (key) DO UPDATE SET ...orDO NOTHING.- The conflict target needs a unique index or constraint.
EXCLUDEDrefers to the row you tried to insert.- It is atomic under concurrency; "select, then insert or update" is not.
Draft outline details
Demonstration
INSERT INTO daily_sales (day, revenue_cents) VALUES ($1, $2)
ON CONFLICT (day) DO UPDATE
SET revenue_cents = daily_sales.revenue_cents + EXCLUDED.revenue_cents;Check your understanding
Why is ON CONFLICT safer than select-then-insert?
Show answer
It is one atomic statement; two sessions cannot both insert.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.