How to Delete Rows in PostgreSQL

Why use DELETE in PostgreSQL?

DELETE quickly removes unwanted data, reclaims storage, and keeps tables clean. Pair it with WHERE to target only the rows you need.

What is the basic DELETE syntax?

Use DELETE FROM table_name WHERE condition;. Omit WHERE to delete every row, or add RETURNING to see affected rows.

How do I delete a single row?

Filter by a unique column.

DELETE FROM users WHERE id = 42 RETURNING *;

How do I delete multiple rows with a condition?

Combine predicates with AND/OR.

DELETE FROM orders
WHERE status = 'expired' AND created_at < NOW() - INTERVAL '30 days';

How can I delete using another table?

JOIN the target and source tables.

DELETE FROM sessions s
USING users u
WHERE s.user_id = u.id AND u.disabled = true;

How do I delete all rows fast?

Use TRUNCATE table_name; for a bulk, logging-minimal operation. It is faster than DELETE without WHERE but cannot be rolled back across different sessions until commit.

How do I limit delete size to avoid locks?

Batch with LIMIT or CTE.

WITH del AS (
DELETE FROM events
WHERE processed = true
LIMIT 1000
RETURNING 1)
SELECT count(*) FROM del;

What are best practices for safe deletes?

1) Always test with SELECT first.
2) Wrap in a transaction so you can ROLLBACK.
3) Keep indexes on filter columns for speed.
4) Use CASCADE rules to manage child rows.

What mistakes should I avoid?

Deleting without WHERE, ignoring foreign-key constraints, and running massive deletes in a single transaction that bloat WAL or lock tables.