# DELETE

> Delete selected rows and understand how DELETE differs from TRUNCATE.

---

LLMS index: [llms.txt](/en/llms.txt)

---

`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 {#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 {#example}

```sql {num="27.1" caption="DELETE"}
-- 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 {#run}

To run (requires `fruits` from earlier lessons):

```text
$ 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 {#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] 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.

> [!NOTE] 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.

## Downloads {#downloads}

SQL file: [delete.sql](/examples/source/delete.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)