DELETE — Removing Rows¶

DELETE is the D in CRUD. Like UPDATE, it lives and dies by its WHERE clause.

Basic Delete¶

sql

1 line

1DELETE FROM tasks WHERE id = 42;

⚠️ Same Trap as UPDATE¶

sql

1 line

1DELETE FROM tasks;   -- 😱 empties the table

Always pair DELETE with a WHERE. Preview with a SELECT first.

Delete with Returning¶

sql

1 line

1DELETE FROM tasks WHERE id = 42 RETURNING *;

Confirms exactly what was removed — useful for logging or undo.

Foreign Keys Constrain Deletes¶

If other rows reference the one you're deleting, the database may block you:

ERROR: update or delete on table "users" violates foreign key constraint

Your options, set when you design the schema (Chapter 2):

ON DELETE CASCADE — remove dependents too.
ON DELETE SET NULL — orphan them.
ON DELETE RESTRICT — forbid the delete while dependents exist.

TRUNCATE vs DELETE¶

sql

2 lines

1TRUNCATE TABLE tasks;        -- wipes ALL rows, fast, resets sequences
2DELETE FROM tasks;           -- row-by-row, fires triggers, can be filtered

TRUNCATE is a blunt instrument — no WHERE, hard to undo. Reach for it only in tests or full resets.

Prefer Soft Deletes for User Data¶

For anything a user might want back — accounts, posts, tasks — a soft delete (Chapter 6) is usually safer than a hard DELETE. Hard delete is for truly disposable or compliance-mandated data.