🌱BeginnerData Manipulation
DELETE
Remove rows from a table with DELETE, including safe patterns and RETURNING.
Overview
DELETE removes rows from a table. Like UPDATE, it is critically important to include a WHERE clause DELETE without WHERE deletes every row in the table (equivalent to TRUNCATE, but slower since it fires triggers and generates WAL per row). Always run a SELECT with the same WHERE first to preview what will be deleted.
PostgreSQL's DELETE supports RETURNING (returning values from deleted rows), USING (joins to filter via another table), and full transaction support so deletions can be rolled back. When you need to delete all rows in a large table quickly, prefer TRUNCATE, which is much faster but cannot be filtered.
Key Points
- •DELETE FROM table WHERE condition always include WHERE
- •DELETE without WHERE removes ALL rows (the table structure remains)
- •RETURNING returns the deleted rows before they are gone
- •USING allows joining another table in the WHERE filter
- •For deleting all rows: TRUNCATE table is faster than DELETE with no WHERE
Examples
Basic DELETE patterns
-- Delete a specific row
DELETE FROM users WHERE id = 42;
-- Delete old records
DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days';
-- Delete and return the deleted rows
DELETE FROM sessions WHERE expires_at < NOW()
RETURNING id, user_id;DELETE with USING (join-based filter)
-- Delete orders for inactive customers
DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id AND c.is_active = FALSE;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4