27 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. DELETE can filter a subset and fires row-level delete triggers. Both commands enforce foreign-key restrictions; TRUNCATE supports its own statement-level triggers.

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

Example

-- 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 marks row versions as deleted and runs applicable triggers and foreign-key checks. Changes to logged tables generate WAL for crash recovery; MVCC preserves the old versions while transactions may still need them.

TRUNCATE table removes all rows without scanning them and is usually much faster for large tables. It is transactional: ROLLBACK restores the data. It takes an ACCESS EXCLUSIVE lock, enforces foreign-key restrictions, and fires ON TRUNCATE statement triggers, never row-level ON DELETE triggers. It is not MVCC-safe for older snapshots; use it deliberately on scratch or staging data.

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

Run the example

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
DROP TABLE
CREATE TABLE
INSERT 0 1
DELETE 1
 id  |   name
-----+-----------
   2 | blueberry
 102 | fruit_2
 103 | fruit_3
(3 rows)

DROP TABLE

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.

Downloads

SQL file: delete.sql · All lesson files · Setup guide