INSERT, UPDATE, DELETE and RETURNING

ConceptFoundation3 min read15 min hands-on#fundamentals#sql

The idea in 30 seconds

INSERT adds rows, UPDATE changes the rows a WHERE clause selects, and DELETE removes them. Leave the WHERE off and the change applies to every row in the table. RETURNING shows you exactly which rows changed, and wrapping a change in BEGIN and ROLLBACK lets you try it without keeping it.

Helps to know first: SQL basics: reading data

Try changes safely first

Every example on this page changes the practice data. Run each one inside a transaction and roll it back, so your shop stays the same as everyone else’s:

BEGIN;
-- … the change …
ROLLBACK;

BEGIN starts a transaction; ROLLBACK undoes everything since. This is also the habit to bring to real data: make the change, look at the result, and only then decide between COMMIT and ROLLBACK. (If you ever damage shop anyway, reload shop.sql.)

INSERT: add rows

INSERT INTO customers (id, name, email, city, joined_on)
VALUES (9, 'Ishaan Gupta', 'ishaan@example.com', 'Delhi', '2025-10-18');

INSERT 0 1

The 1 is the number of rows inserted. Name the columns explicitly, as above: the statement then keeps working if someone adds a column to the table later.

You can insert several rows in one statement, and ask for them back with RETURNING:

INSERT INTO products (id, name, category, price)
VALUES (12, 'Monitor arm', 'Accessories', 3499.00),
       (13, 'Mouse pad',   'Accessories',  399.00)
RETURNING id, name, price;

 id |    name     |  price
----+-------------+---------
 12 | Monitor arm | 3499.00
 13 | Mouse pad   |  399.00

UPDATE: change rows

UPDATE changes every row the WHERE clause matches: none, one or many.

UPDATE products SET price = price * 1.10
WHERE category = 'Audio'
RETURNING name, price;

            name             |  price
-----------------------------+----------
 Noise-cancelling headphones | 14298.90

The new value can be computed from the old one (price * 1.10). Matching nothing is not an error:

UPDATE orders SET status = 'shipped' WHERE id = 999;

UPDATE 0

That 0 is easy to miss in application code. If you expected exactly one row to change, check the count.

DELETE: remove rows

DELETE FROM order_items WHERE order_id = 111 RETURNING *;

 order_id | product_id | quantity | unit_price
----------+------------+----------+------------
      111 |          5 |        2 |    1899.00
      111 |          7 |        1 |    3999.00

Inside our transaction, order_items now has 17 rows. After ROLLBACK, it has 19 again.

Check yourself

What does UPDATE customers SET city = 'Pune'; do?
Show answer
It sets the city of every customer to Pune, because there is no WHERE to limit the rows.
I could explain this without looking
Your code runs UPDATE orders SET status = 'shipped' WHERE id = $1 and reports success. How could nothing have changed?
Show answer
If no order has that id, the update matches zero rows. That is not an error; PostgreSQL returns UPDATE 0. Check the affected row count, or use RETURNING.
I could explain this without looking
Why practice changes inside BEGIN … ROLLBACK?
Show answer
You can run the change, inspect the result, and undo all of it. Only COMMIT makes it permanent.
I could explain this without looking

Sources

Where each claim on this page comes from.

PostgreSQL documentation: 6.2 Updating Data

Primary· §6.2 Updating Data

UPDATE changes the rows that match WHERE; zero, one or many rows may match; with no WHERE, every row in the table is updated.

PostgreSQL documentation: 6.3 Deleting Data

Primary· §6.3 Deleting Data

DELETE removes the rows that match its condition; DELETE without WHERE removes every row.

PostgreSQL documentation: 6.4 Returning Data from Modified Rows

Primary· §6.4 Returning Data from Modified Rows

INSERT, UPDATE, DELETE and MERGE accept RETURNING, which returns data from the modified rows without a separate query.

PostgreSQL documentation: 3.4 Transactions

Primary· §3.4 Transactions

Statements between BEGIN and COMMIT form one transaction; ROLLBACK cancels all of its changes.