# Modifying data > Insert, update and delete rows safely, return what changed with RETURNING, and write upserts with ON CONFLICT and MERGE. SQL & Databases roadmap · Advanced SQL · 17 min read · https://levelcode.org/roadmaps/sql/modifying-data Reading data is only half the job. Every sign-up, checkout and price change is an `INSERT`, `UPDATE` or `DELETE`. These statements are short, but they are also where the most expensive mistakes happen — an `UPDATE` without a `WHERE` rewrites every row. In this lesson you will learn each statement, PostgreSQL's `RETURNING` clause, the two ways to "insert or update" (`ON CONFLICT` and `MERGE`), and a habit that makes data changes safe to try. Reload the bookstore script from the first lesson before you start, so the IDs you see match the ones shown here. ## Try changes safely first Before learning the statements, learn how to undo them. Wrapping work in a transaction lets you look at the result and then throw it away: ```sql BEGIN; UPDATE books SET price = 0; -- oops SELECT title, price FROM books; -- every price is now 0.00 ROLLBACK; -- nothing happened ``` Until you run `COMMIT`, no one else sees your changes, and `ROLLBACK` discards them entirely. The transactions lesson explains how this works; for now, use `BEGIN` … `ROLLBACK` whenever you experiment, and run a `SELECT` with the same `WHERE` clause before any `UPDATE` or `DELETE` to see exactly which rows it will touch. ## INSERT `INSERT` adds rows. Name the columns, then supply values in the same order: ```sql INSERT INTO books (title, author_id, genre, price, published, stock) VALUES ('The Fifth Season', 5, 'fantasy', 14.99, 2015, 10) RETURNING book_id, title; ``` ```text book_id | title ---------+------------------ 11 | The Fifth Season (1 row) INSERT 0 1 ``` N. K. Jemisin finally has a book in the catalogue. A few details: - **Always list the columns.** `INSERT INTO books VALUES (...)` depends on the table's column order and breaks when someone adds a column. - **Omitted columns get their default.** `book_id` is an identity column, so PostgreSQL generated `11`. A column with no default gets `NULL` — or an error, if it is `NOT NULL`. - `INSERT 0 1` is the command tag `psql` prints: one row inserted. You can insert several rows in one statement, which is much faster than many single-row inserts: ```sql INSERT INTO books (title, author_id, genre, price, published) VALUES ('The Obelisk Gate', 5, 'fantasy', 14.99, 2016), ('The Stone Sky', 5, 'fantasy', 14.99, 2017) RETURNING book_id, title, stock; ``` ```text book_id | title | stock ---------+------------------+------- 12 | The Obelisk Gate | 0 13 | The Stone Sky | 0 (2 rows) ``` `stock` was omitted, so it took its declared default, `0`. Because `book_id` is `GENERATED ALWAYS`, supplying a value for it is an error: ```text ERROR: cannot insert a non-DEFAULT value into column "book_id" DETAIL: Column "book_id" is an identity column defined as GENERATED ALWAYS. HINT: Use OVERRIDING SYSTEM VALUE to override. ``` That is a feature: application code cannot accidentally pick IDs that collide with future generated ones. ### RETURNING `RETURNING` makes `INSERT`, `UPDATE`, `DELETE` and `MERGE` return the affected rows, like a `SELECT`. It is how you get a generated ID back without a second query — and without the race condition of "select the max ID". It can return any expression over the affected rows, including `*`: the new values for `INSERT` and `UPDATE`, the removed rows for `DELETE`. ### INSERT … SELECT Instead of `VALUES`, you can insert the result of a query. Create a small table to track books that need restocking, then fill it from `books`: ```sql CREATE TABLE restock_queue ( book_id integer PRIMARY KEY REFERENCES books (book_id), requested_on date NOT NULL DEFAULT current_date ); INSERT INTO restock_queue (book_id) SELECT book_id FROM books WHERE stock < 5 RETURNING book_id; ``` ```text book_id --------- 3 6 10 12 13 (5 rows) ``` ## UPDATE `UPDATE` changes columns in existing rows. `SET` gives the new values; `WHERE` chooses the rows: ```sql UPDATE books SET price = round(price * 1.05, 2) WHERE author_id = 2 RETURNING title, price; ``` ```text title | price -----------------+------- Guards! Guards! | 11.54 Small Gods | 11.54 Going Postal | 12.08 (3 rows) UPDATE 3 ``` Expressions on the right of `SET` see the row's **old** values, so `price * 1.05` uses the current price. That also means `SET a = b, b = a` swaps two columns correctly. You can set several columns at once: `SET price = 9.99, stock = stock + 10`. > [!WARNING] > `UPDATE books SET price = 9.99;` without `WHERE` changes every row in the table. The same goes for `DELETE`. Write the `WHERE` clause first, check it with a `SELECT`, and do risky changes inside `BEGIN` … `ROLLBACK` until you are sure. ### UPDATE … FROM: using other tables Often the new value depends on another table. PostgreSQL's `UPDATE ... FROM` joins extra tables into the update. When order 7 ships, subtract its quantities from stock: ```sql UPDATE books AS b SET stock = b.stock - oi.quantity FROM order_items AS oi WHERE oi.book_id = b.book_id AND oi.order_id = 7 RETURNING b.title, b.stock; ``` ```text title | stock ----------------------+------- A Wizard of Earthsea | 11 (1 row) ``` The `WHERE` clause is the join condition. There is a trap here: **each target row is updated at most once**, even if it matches several rows in `FROM`. Suppose you reserve stock for all March orders in one go. *Going Postal* (stock 3) appears in two March orders, with quantities 1 and 2: ```sql BEGIN; UPDATE books AS b SET stock = b.stock - oi.quantity FROM order_items AS oi JOIN orders AS o USING (order_id) WHERE oi.book_id = b.book_id AND o.ordered_on >= '2025-03-01' RETURNING b.book_id, b.title, b.stock; ROLLBACK; ``` This reports `UPDATE 7` and shows *Going Postal* with stock 2: only one of the two matching lines was applied, and which one is unpredictable. No error, just wrong data. Aggregate first so that each target row has exactly one match: ```sql BEGIN; UPDATE books AS b SET stock = b.stock - t.qty FROM ( SELECT oi.book_id, sum(oi.quantity) AS qty FROM order_items AS oi JOIN orders AS o USING (order_id) WHERE o.ordered_on >= '2025-03-01' GROUP BY oi.book_id ) AS t WHERE t.book_id = b.book_id RETURNING b.book_id, b.title, b.stock; ROLLBACK; ``` Now *Going Postal* correctly drops to 0. (Both experiments end in `ROLLBACK`, so stock is unchanged afterwards.) ## DELETE `DELETE` removes rows matching a `WHERE` clause: ```sql DELETE FROM restock_queue WHERE book_id = 3 RETURNING book_id; ``` Foreign keys protect related data. Chloe has an order, so she cannot be deleted while it exists: ```sql DELETE FROM customers WHERE customer_id = 3; ``` ```text ERROR: update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders" DETAIL: Key (customer_id)=(3) is still referenced from table "orders". ``` Whether a delete should be blocked, or should cascade to related rows, is a design decision covered in the constraints lesson. `DELETE ... USING` is the delete counterpart of `UPDATE ... FROM`. Remove the lines of cancelled orders: ```sql DELETE FROM order_items AS oi USING orders AS o WHERE o.order_id = oi.order_id AND o.status = 'cancelled' RETURNING oi.order_id, oi.book_id; ``` ```text order_id | book_id ----------+--------- 4 | 10 (1 row) DELETE 1 ``` To empty a whole table quickly, `TRUNCATE restock_queue;` is much faster than `DELETE FROM restock_queue;` on large tables, because it discards the table's storage instead of deleting row by row. It refuses to run if other tables reference this one (unless you add `CASCADE`, which empties them too). ### Data-modifying CTEs Because `RETURNING` produces rows, you can chain changes in a single statement with CTEs. This archives cancelled orders: it deletes their items, deletes the orders, and inserts the deleted orders into an archive table — all atomically: ```sql CREATE TABLE cancelled_orders (LIKE orders); WITH gone_items AS ( DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE status = 'cancelled') ), gone_orders AS ( DELETE FROM orders WHERE status = 'cancelled' RETURNING * ) INSERT INTO cancelled_orders SELECT * FROM gone_orders RETURNING order_id, customer_id, status; ``` ```text order_id | customer_id | status ----------+-------------+----------- 4 | 3 | cancelled (1 row) ``` Data-modifying CTEs always run, even if nothing references them (like `gone_items`). All parts of the statement see the same snapshot of the data, so one CTE cannot see another's changes — only its `RETURNING` output. Foreign keys are checked at the end of the statement, which is why deleting items and orders together works. ## Upserts: INSERT … ON CONFLICT "Insert this customer, or update them if the email already exists" is so common it has a name: **upsert**. A plain insert of an existing email fails: ```sql INSERT INTO customers (name, email, city, joined_on) VALUES ('Ada Park', 'ada@example.com', 'York', '2025-04-01'); ``` ```text ERROR: duplicate key value violates unique constraint "customers_email_key" DETAIL: Key (email)=(ada@example.com) already exists. ``` `ON CONFLICT` tells PostgreSQL what to do instead. The special table `EXCLUDED` holds the row you tried to insert: ```sql INSERT INTO customers (name, email, city, joined_on) VALUES ('Ada Park', 'ada@example.com', 'York', '2025-04-01'), ('Gita Shah', 'gita@example.com', 'Bristol', '2025-04-01') ON CONFLICT (email) DO UPDATE SET city = EXCLUDED.city RETURNING customer_id, name, email, city, joined_on; ``` ```text customer_id | name | email | city | joined_on -------------+-----------+------------------+---------+------------ 1 | Ada Park | ada@example.com | York | 2024-01-15 9 | Gita Shah | gita@example.com | Bristol | 2025-04-01 (2 rows) INSERT 0 2 ``` Ada's existing row was updated — her city changed, but her original `joined_on` was kept because `SET` did not mention it — and Gita was inserted. The rules: - The conflict target `(email)` must match a **unique constraint or unique index**. `ON CONFLICT (title)` on `books` fails with "there is no unique or exclusion constraint matching the ON CONFLICT specification". - `ON CONFLICT DO NOTHING` skips conflicting rows silently; `RETURNING` then returns nothing for them. - One statement cannot upsert the same key twice: two rows for `ben@example.com` in one `VALUES` list raise "ON CONFLICT DO UPDATE command cannot affect row a second time". Deduplicate the input first. - Unlike a "check, then insert" done in application code, `ON CONFLICT` is safe when two sessions run it at the same moment. > [!NOTE] > Gita received ID 9, not 7. The failed insert consumed 7 and Ada's conflicting row consumed 8: identity values are taken before conflicts are checked and are never given back, even on rollback. Gaps in IDs are normal — never rely on IDs being consecutive. ## MERGE `MERGE` (added in PostgreSQL 15 and extended in 17) is the SQL-standard way to apply a set of source rows to a target table, with separate actions for matching and non-matching rows. Here a price feed from Terry Pratchett's publisher updates changed prices and adds new titles. Reload the bookstore script first so the prices match the output: ```sql MERGE INTO books AS b USING (VALUES ('Guards! Guards!', 11.99, 1989), ('Small Gods', 10.99, 1992), ('Mort', 10.99, 1987)) AS feed (title, price, published) ON b.title = feed.title AND b.author_id = 2 WHEN MATCHED AND b.price <> feed.price THEN UPDATE SET price = feed.price WHEN NOT MATCHED THEN INSERT (title, author_id, genre, price, published) VALUES (feed.title, 2, 'fantasy', feed.price, feed.published) RETURNING merge_action() AS action, b.book_id, b.title, b.price; ``` ```text action | book_id | title | price --------+---------+-----------------+------- UPDATE | 4 | Guards! Guards! | 11.99 INSERT | 11 | Mort | 10.99 (2 rows) ``` *Small Gods* matched, but its price had not changed, so no `WHEN` clause applied and it was left alone. Two features shown here are new in PostgreSQL 17: `RETURNING` on `MERGE`, and `merge_action()`, which reports what happened to each row. PostgreSQL 17 also adds `WHEN NOT MATCHED BY SOURCE`, which acts on target rows that are absent from the source — for example, marking books missing from a full catalogue feed as discontinued. When to use which? Prefer `INSERT ... ON CONFLICT` for single-row or batch upserts keyed by a unique constraint: it is guaranteed safe under concurrency. Use `MERGE` for batch synchronisation with several conditional actions, including deletes. Under concurrent inserts of the same key, `MERGE` can still fail with a unique violation, so the application must be ready to retry. When you are done experimenting, run `DROP TABLE restock_queue, cancelled_orders;` and reload the bookstore script. ## Practice Starting from a freshly loaded database, do the following inside one transaction, using `RETURNING` to discover generated IDs: 1. Insert a new customer: Gita Shah, `gita@example.com`, Bristol, joined `2025-04-01`, referred by Elena Rossi (customer 5). 2. Create a `paid` order for her dated `2025-04-01`. 3. Add two copies of *Kindred* to the order, recording the book's **current** price as `unit_price` (do not type the price in). 4. Reduce stock for every line of that order using `UPDATE ... FROM`. Commit. Then, in a second transaction, cancel the order and put the stock back. **Solution** ```sql BEGIN; INSERT INTO customers (name, email, city, joined_on, referred_by) VALUES ('Gita Shah', 'gita@example.com', 'Bristol', '2025-04-01', 5) RETURNING customer_id; -- 7 INSERT INTO orders (customer_id, ordered_on, status) VALUES (7, '2025-04-01', 'paid') RETURNING order_id; -- 11 INSERT INTO order_items (order_id, book_id, quantity, unit_price) SELECT 11, book_id, 2, price FROM books WHERE title = 'Kindred' RETURNING *; UPDATE books AS b SET stock = b.stock - oi.quantity FROM order_items AS oi WHERE oi.book_id = b.book_id AND oi.order_id = 11 RETURNING b.title, b.stock; COMMIT; ``` ```text order_id | book_id | quantity | unit_price ----------+---------+----------+------------ 11 | 7 | 2 | 14.00 (1 row) title | stock ---------+------- Kindred | 9 (1 row) ``` `INSERT ... SELECT` copies the current price from `books`, so the order line records what the customer actually paid even if the price changes later. Your IDs may differ if you ran other inserts first — use the values your `RETURNING` clauses printed. ```sql BEGIN; UPDATE orders SET status = 'cancelled' WHERE order_id = 11; UPDATE books AS b SET stock = b.stock + oi.quantity FROM order_items AS oi WHERE oi.book_id = b.book_id AND oi.order_id = 11 RETURNING b.title, b.stock; -- Kindred | 11 COMMIT; ``` Doing each set of changes in a transaction means an error halfway through (say, a typo in the third statement) cannot leave an order with no items or stock that was never decremented. ## Key takeaways - Always list columns in `INSERT`; use multi-row `VALUES` or `INSERT ... SELECT` for bulk inserts, and `RETURNING` to get generated values back. - `UPDATE` and `DELETE` affect every row their `WHERE` matches — check with a `SELECT` first and experiment inside `BEGIN` … `ROLLBACK`. - `UPDATE ... FROM` updates each target row at most once; aggregate the source first when several source rows match. - Data-modifying CTEs chain inserts, updates and deletes atomically in one statement. - `INSERT ... ON CONFLICT` is the concurrency-safe upsert and needs a matching unique constraint; `MERGE` handles richer synchronisation, and in PostgreSQL 17 supports `RETURNING` and `merge_action()`. ## Quiz 1. A row is inserted with INSERT ... ON CONFLICT (email) DO UPDATE SET city = EXCLUDED.city. What does EXCLUDED refer to? A. The existing row already in the table B. Rows deleted by the statement C. The row that was proposed for insertion but conflicted D. Rows excluded by a WHERE clause **Answer: C.** EXCLUDED is the row you tried to insert. The existing row is referenced by the table name (or its alias), so SET city = EXCLUDED.city copies the new city onto the existing row. 2. What must exist for INSERT ... ON CONFLICT (title) DO NOTHING to work on books? A. An index of any kind on title B. A unique constraint or unique index on title C. A NOT NULL constraint on title D. A primary key on title specifically **Answer: B.** The conflict target must match a unique (or exclusion) constraint or unique index. Without one, PostgreSQL raises 'there is no unique or exclusion constraint matching the ON CONFLICT specification'. A primary key works, but any unique constraint does. 3. An INSERT into customers fails with a unique violation. What happens to the identity value it was assigned? A. It is consumed and never reused, leaving a gap B. It is returned and used by the next insert C. It is reused only if the transaction is rolled back D. The sequence is reset to the table's maximum ID **Answer: A.** Sequence and identity values are allocated outside transactional rollback for performance and concurrency. Failed inserts, rollbacks and ON CONFLICT all leave gaps, so never assume IDs are consecutive. 4. UPDATE books b SET stock = b.stock - oi.quantity FROM order_items oi WHERE oi.book_id = b.book_id; — a book appears in three order lines. What happens to that book? A. Its stock is reduced by the sum of all three quantities B. The statement fails with an error about multiple matches C. Its stock is reduced three times in sequence D. Its stock is reduced by only one of the quantities, and which one is unpredictable **Answer: D.** In UPDATE ... FROM each target row is updated at most once, using an arbitrary one of its matching source rows, with no error. Aggregate the source in a subquery (sum per book_id) so each book matches exactly one row.