Chapter 10: PostgreSQL UPDATE
PostgreSQL UPDATE ☕✏️
You’ve already:
- Created tables
- Inserted rows
- Selected / read data
- Added columns to existing tables
Now the real world hits: students change phone numbers, GPAs get updated after revaluation, enrollment status changes, fees get marked paid… that’s UPDATE time.
1. What does UPDATE actually do? (Plain teacher talk)
UPDATE tells PostgreSQL:
“Find the rows that match my condition (WHERE clause), and for those rows only, change the values in the specified columns to these new values. Respect all constraints (CHECK, UNIQUE, foreign keys, NOT NULL). If anything would violate a rule → abort the whole operation (atomicity).”
Important 2026 facts (PostgreSQL 18.x):
- Very safe — never accidentally wipes data if you use WHERE
- Can update one row, many rows, or the entire table (dangerous!)
- Supports RETURNING clause → shows you what changed (very useful in apps)
- PostgreSQL 18 improved RETURNING → you can now access both OLD and NEW values easily
- Can use expressions, calculations, subqueries, even JOINs in FROM clause
Official one-liner from docs (postgresql.org/docs/current/sql-update.html):
UPDATE changes the values of the specified columns in all rows that satisfy the condition.
2. Basic syntax skeleton (the shapes you’ll use daily)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
UPDATE table_name SET column1 = new_value_or_expression, column2 = another_value, ... [FROM other_table_or_join] [WHERE condition] [RETURNING * | column_list | expression AS alias, ...]; |
- SET — what to change and to what
- WHERE — which rows (forget this → updates everything — scary!)
- RETURNING — optional, shows updated rows (like INSERT)
3. Real example — using our students table
Current data snapshot (assume after your previous INSERTs and ADD COLUMN):
| id | first_name | last_name | gpa | phone_number | fees_paid | enrollment_year | is_active | |
|---|---|---|---|---|---|---|---|---|
| 1 | Aarav | Patel | aarav.patel@hyduni.edu | 8.95 | NULL | FALSE | 2025 | true |
| 2 | Priya | Reddy | priya.reddy22@gmail.com | 9.10 | NULL | FALSE | 2024 | true |
| 3 | Rahul | Sharma | rahul.sharma.2004@yahoo.com | 7.85 | NULL | FALSE | 2025 | true |
| 4 | Sneha | Kumar | sneha.kumar@outlook.com | 8.60 | NULL | FALSE | 2025 | true |
| 5 | Meera | Iyer | meera.iyer@gmail.com | 9.75 | NULL | FALSE | 2025 | true |
Example 1: Update one specific row (most common & safest)
|
0 1 2 3 4 5 6 7 8 9 |
UPDATE students SET phone_number = '+91-98765-43210', fees_paid = TRUE WHERE email = 'aarav.patel@hyduni.edu'; |
→ Message: UPDATE 1 (one row changed) → Aarav now has phone & fees paid = TRUE
Example 2: Update with calculation (raise GPA by 0.5 for top students)
|
0 1 2 3 4 5 6 7 8 |
UPDATE students SET gpa = gpa + 0.5 WHERE gpa >= 9.0; |
→ Priya (9.10 → 9.60), Meera (9.75 → 10.00) get bump → UPDATE 2
Example 3: Update multiple columns + use DEFAULT
|
0 1 2 3 4 5 6 7 8 9 10 |
UPDATE students SET is_active = FALSE, fees_paid = DEFAULT -- resets to FALSE if default was FALSE WHERE enrollment_year < 2025; |
Example 4: Very useful — UPDATE + RETURNING (see what changed)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
UPDATE students SET gpa = 9.20 WHERE first_name = 'Sneha' AND last_name = 'Kumar' RETURNING id, first_name || ' ' || last_name AS full_name, gpa AS new_gpa; |
→ Returns something like:
|
0 1 2 3 4 5 6 7 8 |
id | full_name | new_gpa ----+---------------+--------- 4 | Sneha Kumar | 9.20 |
PostgreSQL 18 special: access OLD & NEW in RETURNING
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
UPDATE students SET gpa = gpa * 1.05 -- 5% grace marks WHERE gpa < 8.0 RETURNING first_name || ' ' || last_name AS student, OLD.gpa AS old_gpa, NEW.gpa AS new_gpa; |
→ Shows before & after — super helpful for audit logs or apps!
4. UPDATE with JOIN (very powerful — update from another table)
Assume we have courses and enrollments tables.
Update grades based on some logic:
|
0 1 2 3 4 5 6 7 8 9 10 11 |
UPDATE enrollments e SET grade = 'A+' FROM students s WHERE e.student_id = s.id AND s.gpa >= 9.0 AND e.course_id = 1; -- e.g. CS101 course |
→ Only high-GPA students get A+ in that course
5. Common dangers & best practices (real student lessons)
| Mistake | What happens | Prevention |
|---|---|---|
| Forget WHERE clause | Updates every single row | Always write WHERE first, test with SELECT |
| WHERE condition matches nothing | UPDATE 0 — nothing happens (safe) | Test first: SELECT * FROM … WHERE … |
| Violate CHECK / UNIQUE / FK | Error, nothing updated | Good — data stays consistent |
| Update primary key | Possible, but dangerous (cascades?) | Avoid unless you know |
| Large table without index on WHERE | Slow (full table scan) | Index columns used in WHERE |
Always test first pattern (guru rule):
|
0 1 2 3 4 5 6 7 8 9 10 |
-- Test what would be updated SELECT * FROM students WHERE gpa < 8.0; -- Then run UPDATE students SET ... WHERE gpa < 8.0; |
6. Your mini practice right now
- Give Aarav a phone number and mark fees_paid = TRUE
- Raise GPA by 0.3 for all students with GPA < 9.0
- Use RETURNING to see the updated rows
- Try the OLD/NEW syntax if you’re on PostgreSQL 18
Next class?
Tell me:
- Want DELETE next (remove rows)?
- UPSERT (INSERT … ON CONFLICT … DO UPDATE)?
- Transactions (BEGIN / COMMIT / ROLLBACK) with UPDATE?
- Or bulk updates / performance tips?
Your teacher is still right here — what’s next? 🚀
