Chapter 13: PostgreSQL DELETE
PostgreSQL: DELETE ☕🗑️
You’ve already learned how to:
- Create tables
- Insert rows
- Select / read data
- Update existing rows
- Add, alter, drop columns
Now comes DELETE — the command that permanently removes rows from a table.
1. What does DELETE really do? (Teacher’s very honest explanation)
DELETE tells PostgreSQL:
“Find all rows in this table that match my condition (WHERE clause), and permanently remove them from the table. Respect foreign key constraints (unless CASCADE is used). If the row is referenced by other tables → either fail or cascade-delete depending on the constraint definition.”
Very important PostgreSQL behaviors (still true in version 18.x in 2026):
- DELETE is row-by-row — it physically marks rows as dead (creates dead tuples), but does not immediately free disk space
- Space is reclaimed later by autovacuum or manual VACUUM
- DELETE can be very slow on large tables if no proper index exists on the WHERE condition
- DELETE triggers any ON DELETE triggers you defined
- DELETE respects transactions → you can ROLLBACK if you make a mistake
- There is no UNDO outside of a transaction — once COMMITted, rows are gone forever (unless you have backups / WAL archiving)
Official one-liner from PostgreSQL docs:
DELETE deletes rows that satisfy the WHERE clause from the specified table.
2. Basic syntax patterns (the ones you will use 95% of the time)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
-- Most common & safest DELETE FROM table_name WHERE condition; -- Delete everything (very dangerous!) DELETE FROM table_name; -- With RETURNING (see what was deleted) DELETE FROM table_name WHERE ... RETURNING *; -- Multiple tables (advanced – using USING / FROM clause) DELETE FROM table_a USING table_b WHERE table_a.id = table_b.id AND table_b.status = 'expired'; |
3. Real example – using our college students table
Current snapshot (assume we have ~10 students inserted):
| id | first_name | last_name | gpa | enrollment_year | fees_paid | is_active | |
|---|---|---|---|---|---|---|---|
| 1 | Aarav | Patel | aarav.patel@hyduni.edu | 8.95 | 2025 | TRUE | true |
| 2 | Priya | Reddy | priya.reddy22@gmail.com | 9.60 | 2024 | FALSE | true |
| 3 | Rahul | Sharma | rahul.sharma.2004@yahoo.com | 7.85 | 2025 | FALSE | false |
| 4 | Sneha | Kumar | sneha.kumar@outlook.com | 9.20 | 2025 | TRUE | true |
| 5 | Meera | Iyer | meera.iyer@gmail.com | 10.00 | 2025 | TRUE | true |
| 6 | Kiran | Joshi | … | … | 2023 | FALSE | false |
Example 1: Delete one specific row (safest & most common)
|
0 1 2 3 4 5 6 7 |
DELETE FROM students WHERE email = 'rahul.sharma.2004@yahoo.com'; |
→ Message: DELETE 1 → Rahul is gone forever
Example 2: Delete inactive students
|
0 1 2 3 4 5 6 7 |
DELETE FROM students WHERE is_active = FALSE; |
→ Deletes Kiran (and any others inactive) → DELETE 1 (or more)
Example 3: Delete old students + see what was removed (RETURNING)
|
0 1 2 3 4 5 6 7 8 9 10 11 |
DELETE FROM students WHERE enrollment_year < 2025 RETURNING id, first_name || ' ' || last_name AS full_name, enrollment_year; |
→ Might return:
|
0 1 2 3 4 5 6 7 8 |
id | full_name | enrollment_year ----+---------------+----------------- 6 | Kiran Joshi | 2023 |
Example 4: Delete using JOIN logic (advanced but very useful)
Assume we have enrollments table and we want to remove students who never enrolled in any course:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
DELETE FROM students s USING ( SELECT student_id FROM enrollments GROUP BY student_id ) e WHERE s.id NOT IN (SELECT student_id FROM enrollments); |
(or more efficiently with LEFT JOIN)
|
0 1 2 3 4 5 6 7 8 9 |
DELETE FROM students s WHERE NOT EXISTS ( SELECT 1 FROM enrollments e WHERE e.student_id = s.id ); |
4. DELETE vs TRUNCATE (very important comparison – 2026 reality)
| Feature | DELETE | TRUNCATE |
|---|---|---|
| Removes rows | Selected rows (WHERE) | All rows (cannot WHERE) |
| Triggers fire | Yes | No |
| Foreign keys respected | Yes (errors or CASCADE) | No (needs CASCADE option) |
| Can be rolled back | Yes | Yes (in transaction) |
| Speed on large table | Slow (row-by-row, writes to WAL) | Extremely fast (metadata operation) |
| Resets sequences (SERIAL) | No | Yes (by default) |
| Vacuum needed after | Yes (dead tuples) | No |
| Typical use case | Remove some bad/old data | Clear entire table (staging, testing, reset) |
Rule of thumb:
- Use DELETE when you need WHERE or care about triggers/FK
- Use TRUNCATE when you want to wipe the whole table quickly
|
0 1 2 3 4 5 6 |
TRUNCATE TABLE temp_import_table RESTART IDENTITY CASCADE; |
5. Common dangers & best practices (real production lessons)
| Mistake | Consequence | Prevention |
|---|---|---|
| Forget WHERE clause | Deletes entire table | Always write WHERE first — test with SELECT |
| DELETE on table referenced by FK | Error (unless ON DELETE CASCADE) | Check constraints first |
| No index on WHERE column | Very slow on large table (table scan) | Index frequently filtered columns |
| DELETE millions of rows in one go | Long transaction → locks, WAL bloat, slow | Delete in batches (e.g. 10,000 rows at a time) |
| No transaction | Cannot rollback after COMMIT | BEGIN; DELETE …; SELECT COUNT(*) …; COMMIT; or ROLLBACK; |
Safe batch delete pattern (very common in production):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DO $$ DECLARE rows_deleted INTEGER; BEGIN LOOP DELETE FROM students WHERE enrollment_year < 2024 LIMIT 5000; GET DIAGNOSTICS rows_deleted = ROW_COUNT; EXIT WHEN rows_deleted = 0; COMMIT; -- or do COMMIT every 10k rows RAISE NOTICE 'Deleted % rows', rows_deleted; END LOOP; END $$; |
6. Your mini practice right now
- Delete one student by email
- Delete all students with GPA < 8.0 (use RETURNING)
- Try DELETE FROM students; → see how many rows it says (then ROLLBACK if in transaction!)
- Run SELECT COUNT(*) FROM students; before & after
Next class?
Tell me:
- Want TRUNCATE in detail?
- ON CONFLICT / UPSERT (INSERT or UPDATE)?
- Transactions (BEGIN / COMMIT / ROLLBACK / SAVEPOINT) with DELETE examples?
- Or move to constraints, indexes, foreign keys in depth?
Your teacher is still here — what’s next? 🚀
