Postgres by Example: DELETE

DELETE FROM table removes rows. Like UPDATE, the WHERE clause is the difference between "remove these specific rows" and "wipe the whole table." Like UPDATE, DELETE is transactional — a ROLLBACK undoes it. Unlike TRUNCATE, DELETE fires triggers, respects foreign keys, and can be filtered to a subset.

What you'll learn:

• Deleting rows with DELETE FROM ... WHERE
• Why TRUNCATE exists alongside DELETE
• DELETE ... USING for joining to a source table
• Returning deleted rows with RETURNING
• Transactional safety, foreign-key cascades, and triggers

-- Demo on a temp table so we don't drain `fruits`
DROP TABLE IF EXISTS to_delete;
CREATE TABLE to_delete AS SELECT * FROM fruits;

-- Delete a single row
DELETE FROM to_delete WHERE id = 1;

-- Delete by membership
DELETE FROM to_delete WHERE name IN ('chestnut', 'damson');

-- Delete returning the removed rows
DELETE FROM to_delete WHERE id = 5 RETURNING id, name;

-- Delete using another table as a source (DELETE ... USING)
DROP TABLE IF EXISTS keep;
CREATE TABLE keep (id integer);
INSERT INTO keep SELECT id FROM to_delete LIMIT 1;

DELETE FROM to_delete AS d USING keep k WHERE d.id = k.id;

SELECT * FROM to_delete ORDER BY id;

-- Clean up
DROP TABLE to_delete;
DROP TABLE keep;

DELETE removes one row at a time internally (firing per-row triggers and checking foreign keys) and is journaled — every row deleted is recorded in the WAL so it can be replayed or rolled back. That makes it safe but not always the fastest.

TRUNCATE table is the heavy-hitter cousin: it removes every row, skips per-row triggers (you can opt back in), and is much faster on large tables. It is not transactional safe in the same way — if you TRUNCATE and ROLLBACK, the rows do come back (good!) but the operation takes an ACCESS EXCLUSIVE lock and is generally considered destructive. Use it for clearing staging tables, never on production data without thought.

DELETE ... USING is the analog of UPDATE ... FROM: lets you express "delete rows in t that match rows in s" by joining to s.

To run (requires fruits from earlier lessons):

$ psql -f source/delete.sql postgres
DROP TABLE
SELECT 8
DELETE 1
DELETE 2
 id |    name
----+------------
  5 | elderberry
(1 row)
DELETE 1
CREATE TABLE
INSERT 0 1
DELETE 1
 id |  name
----+--------
  3 | cherry
  4 | date
...

Common pitfalls:

• Forgetting WHERE — same warning as UPDATE, even more painful. Always preview with SELECT count(*) FROM t WHERE same_condition; first.
• DELETE on a table with ON DELETE CASCADE foreign keys can fan out across many child tables. Audit before running.
• DELETE does not reclaim disk space — the rows are marked dead and reclaimed by VACUUM later. After a huge delete, run VACUUM (VERBOSE) t; to reclaim and ANALYZE to refresh statistics.
• Soft deletes (a deleted_at column instead of a real DELETE) are common in app design. Pick a strategy and stick to it.

Tip: For very large deletes, batch them: DELETE FROM logs WHERE created_at < now() - interval '30 days' AND id IN (SELECT id FROM logs WHERE created_at < ... LIMIT 10000);. This keeps each transaction small, autovacuum can keep up, and lock holds are short.

Try it: Add a row to a temp table and delete it. Then try BEGIN; DELETE FROM fruits; SELECT count(*) FROM fruits; ROLLBACK; SELECT count(*) FROM fruits; — the rollback should restore the rows. Then try TRUNCATE on a temp table.

Source: delete.sql

Next: RETURNING

Home: Postgres by Example