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:

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:

INSERT INTO books (title, author_id, genre, price, published, stock)
VALUES ('The Fifth Season', 5, 'fantasy', 14.99, 2015, 10)
RETURNING book_id, title;

 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:

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;

 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:

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:

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;

 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:

UPDATE books
SET price = round(price * 1.05, 2)
WHERE author_id = 2
RETURNING title, price;

      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.

#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:

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;

        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:

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:

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:

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:

DELETE FROM customers WHERE customer_id = 3;

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:

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;

 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:

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;

 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:

INSERT INTO customers (name, email, city, joined_on)
VALUES ('Ada Park', '[email protected]', 'York', '2025-04-01');

ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=([email protected]) already exists.

ON CONFLICT tells PostgreSQL what to do instead. The special table EXCLUDED holds the row you tried to insert:

INSERT INTO customers (name, email, city, joined_on)
VALUES ('Ada Park',  '[email protected]',  'York',    '2025-04-01'),
       ('Gita Shah', '[email protected]', 'Bristol', '2025-04-01')
ON CONFLICT (email) DO UPDATE
SET city = EXCLUDED.city
RETURNING customer_id, name, email, city, joined_on;

 customer_id |   name    |      email       |  city   | joined_on
-------------+-----------+------------------+---------+------------
           1 | Ada Park  | [email protected]  | York    | 2024-01-15
           9 | Gita Shah | [email protected] | 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 [email protected] 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.

#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:

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;

 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, [email protected], 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.

BEGIN;

INSERT INTO customers (name, email, city, joined_on, referred_by)
VALUES ('Gita Shah', '[email protected]', '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;

 order_id | book_id | quantity | unit_price
----------+---------+----------+------------
       11 |       7 |        2 |      14.00
(1 row)

  title  | stock
---------+-------
 Kindred |     9
(1 row)

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;

#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().