Lesson 9 of 15 · 22 min
Upserts and RETURNING
By the end of this lesson you can write INSERT … ON CONFLICT DO NOTHING and DO UPDATE using EXCLUDED, return changed rows with RETURNING, and move rows between tables in one statement with a data-modifying CTE.
An upsert is an insert that knows what to do when the row already exists. In PostgreSQL you write a normal INSERT and add ON CONFLICT:
ON CONFLICT (key) DO NOTHINGskips rows whose key already exists and inserts the rest.ON CONFLICT (key) DO UPDATE SET …updates the existing row instead.
The conflict target (key) names the column(s) whose uniqueness decides whether a row "already exists". It must match a primary key or unique constraint (or unique index); otherwise PostgreSQL refuses with there is no unique or exclusion constraint matching the ON CONFLICT specification. That is why constraints from lessons 3 and 5 matter here: the database can only detect a duplicate it has been told about.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons