Lesson 7: Make changes inside a safe transaction
Practice data changes on a development database first. Use explicit column names, narrow predicates, and parameterized statements from application code.
BEGIN;
UPDATE orders
SET status = 'shipped'
WHERE order_id = 1042
AND status = 'paid'
RETURNING order_id, status;
-- COMMIT after checking the returned row; otherwise ROLLBACK.Data-change statements
10 patterns shown
| Statement | Purpose | Example |
|---|---|---|
| INSERT | Add rows with named columns. | INSERT INTO customers(name,email) VALUES ('Ada','ada@example.com'); |
| INSERT multiple | Add several rows in one statement. | VALUES ('A','a@example.com'), ('B','b@example.com') |
| INSERT SELECT | Insert the result of a query. | INSERT INTO archive_orders SELECT * FROM orders WHERE created_at < DATE '2020-01-01'; |
| RETURNING | Return affected rows in PostgreSQL. | INSERT INTO items(name) VALUES ('Pen') RETURNING id; |
| UPDATE | Change selected rows. | UPDATE products SET price=price*1.05 WHERE category_id=3; |
| DELETE | Remove selected rows; check the WHERE clause. | DELETE FROM sessions WHERE expires_at < CURRENT_TIMESTAMP; |
| ON CONFLICT | PostgreSQL UPSERT for a unique key. | ON CONFLICT (email) DO UPDATE SET name=EXCLUDED.name |
| BEGIN | Start a transaction block. | BEGIN; |
| COMMIT | Persist the transaction's changes. | COMMIT; |
| ROLLBACK | Discard changes in the current transaction. | ROLLBACK; |
Safe change checklist
- Run a SELECT with the same WHERE clause and confirm intended rows.
- Use a transaction when the operation has multiple dependent steps.
- Check row counts and RETURNING results before commit.
- Use foreign keys and unique constraints to prevent invalid state.
- Use prepared statements for data supplied by users.
Progress stays in this browser.