Deleting Data
1. Deleting Specific Rows
DELETE FROM customers WHERE id = 42;
| Best Practice | Why |
|---|
| WHERE PK | Most efficient and safe |
| LIMIT via CTE | Batch deletes to avoid long locks |
2. Deleting All Rows
DELETE FROM staging; -- row by row (slower, generates WAL)
TRUNCATE staging; -- DDL, fast, also frees space
3. Deleting with Subquery
DELETE FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE status='deactivated');
WITH del_orders AS (
DELETE FROM orders WHERE customer_id = 42 RETURNING id
)
DELETE FROM order_items WHERE order_id IN (SELECT id FROM del_orders);
5. Using RETURNING Clause
DELETE FROM events WHERE created_at < now() - interval '90 days'
RETURNING id, created_at;
| Use | Pattern |
|---|
| Audit log | Insert RETURNING row into archive |
| Cascading work | Feed CTE for downstream updates |
6. Deleting with JOIN
DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id AND c.status = 'deactivated';
Note: PostgreSQL uses USING (not JOIN) for multi-table DELETE.
7. Truncating Table
| vs DELETE | TRUNCATE |
|---|
| Speed | O(1) (drops files, no row scan) |
| Triggers | Fires TRUNCATE triggers only |
| Lock | ACCESS EXCLUSIVE |
| Bloat | Releases space immediately |
8. Truncating Multiple Tables
TRUNCATE orders, order_items;
9. Truncating with CASCADE
TRUNCATE customers RESTART IDENTITY CASCADE;
| Behavior | Effect |
|---|
| CASCADE | Auto-truncate FK-referencing tables |
| RESTRICT | Default; error if FKs exist |
10. Restarting Identity Sequences
TRUNCATE orders RESTART IDENTITY;
-- Or manually
SELECT setval(pg_get_serial_sequence('orders','id'), 1, false);
| Option | Behavior |
|---|
| RESTART IDENTITY | Reset sequences to starting value |
| CONTINUE IDENTITY | Default; keep last allocated value |