DELETE and TRUNCATE

Removes rows that match a condition (DELETE) or all rows quickly (TRUNCATE).

Syntax

DELETE FROM table [AS t]
[USING other_table]
[WHERE condition]
[RETURNING ...];

TRUNCATE [TABLE] table [, ...] [RESTART IDENTITY] [CASCADE];

Without WHERE, every row is deleted. Preview with SELECT using the same condition first.
DELETE ... USING other deletes rows that join to another table.
Foreign keys: deleting a parent row that child rows reference fails unless the constraint has ON DELETE CASCADE / SET NULL. Delete children first, or reconsider (often a status column is better than deleting).
TRUNCATE empties whole tables instantly, does not fire row triggers, and needs CASCADE if other tables reference it. It is transactional in PostgreSQL (can be rolled back).
Deleted rows' space is reclaimed later by VACUUM.

Examples

Delete with a preview (modifies data) starter

SELECT count(*) AS to_delete FROM orders WHERE status = 'cancelled';

DELETE FROM orders
WHERE status = 'cancelled'
RETURNING order_id, customer_id;

The preview count should equal the number of rows returned by DELETE.

DELETE ... USING (modifies data) starter

DELETE FROM orders o
USING products p
WHERE p.product_id = o.product_id
  AND p.category = 'Electronics'
  AND o.status = 'processing'
RETURNING o.order_id;

USING joins another table to decide which rows to delete.

Pitfalls

Forgetting WHERE.
Foreign-key violation when deleting referenced parent rows.
Using TRUNCATE when only some rows should go.
Deleting rows that history depends on, instead of marking them inactive.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

MySQL: Multi-table DELETE t FROM t JOIN other ...; TRUNCATE commits implicitly and cannot be rolled back.
SQL Server: DELETE t FROM t JOIN other ...; OUTPUT deleted.* instead of RETURNING.
Oracle: TRUNCATE is DDL and commits implicitly.