Module 13 — DELETE

1. DELETE — Removing Data

What it is: DELETE FROM is a DML command that permanently removes rows from a table. You specify which rows to delete using a WHERE clause — without it, all rows are deleted.

Why we use it: Data sometimes needs to be removed — test data cleanup, GDPR compliance, removing spam, or clearing temporary records. DELETE is the tool for permanent removal.

When we use it: When removing specific records, cleaning up test data, complying with data retention policies, or clearing temporary/staging data.

-- Delete a specific user
DELETE FROM users
WHERE id = 4;

2. The "Danger Zone" — Missing WHERE

What it is: Without WHERE, DELETE removes every row in the table. This is almost always a catastrophic mistake — in production, it can destroy millions of records instantly.

Why we use it: This section exists to warn you. Like UPDATE, forgetting WHERE on DELETE is one of the most common causes of data loss. Always write WHERE first, then the rest of the DELETE.

When we use it: Never intentionally. This is a mistake to avoid.

-- DANGER: The table is now empty!
DELETE FROM users;

3. DELETE with RETURNING

What it is: RETURNING (PostgreSQL extension) returns the deleted rows immediately after the DELETE executes. It's like combining DELETE and SELECT in one atomic operation.

Why we use it: After deleting a record, you might need to log what was removed, return the deleted data to the client, or verify what was affected.

When we use it: In audit logging, REST APIs that return deleted resources, or when you need to verify what was removed.

DELETE FROM users
WHERE id = 4
RETURNING *;

4. Soft Delete vs Hard Delete

What it is: Soft delete is a pattern where instead of actually removing a row, you mark it as deleted (usually with a deleted_at timestamp). The row still exists in the database but is hidden from queries. Hard delete permanently removes the row.

Why we use it: Soft delete allows data recovery, preserves referential integrity (no orphaned records), maintains audit trails, and supports analytics on deleted data. It's the standard approach for user-facing applications.

When we use it: Use soft delete for important data (users, posts, orders) and hard delete for temporary data (sessions, logs, test data).

Implementing Soft Delete

-- Add a soft delete column
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;
 
-- "Delete" a user (soft delete)
UPDATE users
SET deleted_at = NOW()
WHERE id = 5;
 
-- Query active users (exclude soft-deleted)
SELECT * FROM users WHERE deleted_at IS NULL;
 

5. Safe DELETE Pattern

What it is: The safe DELETE pattern is a workflow: always test your WHERE clause with SELECT first, verify the rows that will be deleted, then run the DELETE with the same WHERE clause.

Why we use it: This pattern prevents accidental mass deletes. By previewing which rows will be affected, you can catch mistakes before they cause permanent data loss.

When we use it: Every time you run a DELETE in production. It takes 5 seconds and can save hours of recovery work (or worse, unrecoverable data loss).

-- Step 1: Test with SELECT
SELECT * FROM users WHERE is_active = false;
 
-- Step 2: Verify the rows you want to delete
-- Step 3: Run DELETE with the same WHERE clause
DELETE FROM users WHERE is_active = false;

Common Mistakes to Avoid

1. Forgetting WHERE — Deletes all rows (disaster)
2. Hard deleting user data — Use soft deletes for anything recoverable
3. Not using transactions — Wrap in BEGIN/COMMIT for safety
4. Cascade deletes — Deleting a user may cascade-delete their posts (check FK constraints)

Practice Exercise

Using your courses table:

1. Delete one course using its id
2. Safe Test: Start a transaction, delete all courses, verify with SELECT, then ROLLBACK
3. Add a deleted_at column to your courses table
4. Implement soft delete: "delete" a course by setting deleted_at
5. Write a query that only shows non-deleted courses