DELETE

Learning Focus

Use this lesson to understand DELETE with practical syntax and examples.

Concept Overview

Definition

DELETE removes rows from a table. It is commonly used for retention cleanup (expired sessions, old logs), correcting invalid imports, and compliance-driven removal of data.

PostgreSQL provides features that make deletions safer and easier to verify:

• RETURNING to see exactly which rows were removed
• transactions to allow rollback while you verify
• DELETE ... USING to delete rows that match a join

Why is it important?

• Data quality: removing incorrect or duplicated rows keeps reports and application behavior accurate
• Safety: an accidental DELETE can remove the wrong data set permanently
• Integrity: foreign keys can block deletes or cascade them to dependent tables
• Operations: large deletes can create lock pressure and bloat; choosing the right approach matters

Where does it fit?

DELETE is part of DML (Data Manipulation Language), alongside SELECT, INSERT, and UPDATE. In CRUD terms, it is the Delete operation.

---

Syntax & Rules

Core Syntax

DELETE FROM table_name
WHERE condition
RETURNING returning_expressions;

Available Options / Parameters

Part	What it does	Notes (PostgreSQL)
WHERE	selects which rows to delete	omit only if you truly want to delete every row
RETURNING	returns deleted rows	best for verification and auditing
USING	joins additional sources	DELETE ... USING deletes based on a join
WITH (CTE)	stages rows for deletion	useful for batching and deterministic deletes

Key Rules and Considerations

• Always preview with SELECT before deleting.
• Foreign keys may block deletion unless dependents are handled first or the FK is defined with ON DELETE CASCADE.
• Deleting rows does not automatically shrink the table file; space becomes reusable and may require vacuuming.
• For “delete everything”, TRUNCATE is typically faster but has different semantics.
• Consider soft delete (deleted_at) when you need recovery, audit trails, or delayed cleanup.

---

Step-by-Step Examples

Example 1: Delete One Row + Verify with RETURNING (Beginner)

CREATE TABLE sessions (
  session_id  text PRIMARY KEY,
  user_id     bigint NOT NULL,
  expires_at  timestamptz NOT NULL
);

INSERT INTO sessions (session_id, user_id, expires_at)
VALUES
  ('s1', 10, '2026-03-01 00:00:00+00'),
  ('s2', 11, '2026-04-01 00:00:00+00');

-- Preview
SELECT session_id, user_id, expires_at
FROM sessions
WHERE session_id = 's1';

-- Delete + verify
DELETE FROM sessions
WHERE session_id = 's1'
RETURNING session_id, user_id;

Expected output (preview):

 session_id | user_id | expires_at
------------+---------+------------------------
 s1         | 10      | 2026-03-01 00:00:00+00
(1 row)

Expected output (RETURNING):

 session_id | user_id
------------+---------
 s1         | 10
(1 row)

---

Example 2: Delete Many Rows by Condition (Intermediate)

-- Add more sessions
INSERT INTO sessions (session_id, user_id, expires_at)
VALUES
  ('s3', 10, '2026-02-01 00:00:00+00'),
  ('s4', 12, '2026-01-01 00:00:00+00');

-- Preview expired sessions
SELECT session_id, user_id, expires_at
FROM sessions
WHERE expires_at < '2026-03-05 00:00:00+00'
ORDER BY expires_at;

-- Delete expired sessions and log what was removed
DELETE FROM sessions
WHERE expires_at < '2026-03-05 00:00:00+00'
RETURNING session_id, user_id, expires_at
ORDER BY expires_at;

Expected output (preview):

 session_id | user_id | expires_at
------------+---------+------------------------
 s4         | 12      | 2026-01-01 00:00:00+00
 s3         | 10      | 2026-02-01 00:00:00+00
(2 rows)

Expected output (RETURNING):

 session_id | user_id | expires_at
------------+---------+------------------------
 s4         | 12      | 2026-01-01 00:00:00+00
 s3         | 10      | 2026-02-01 00:00:00+00
(2 rows)

---

Example 3: Delete Based on a Join with DELETE ... USING (Advanced)

CREATE TABLE customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       text NOT NULL UNIQUE,
  is_active   boolean NOT NULL DEFAULT true
);

CREATE TABLE customer_events (
  event_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  event_type  text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO customers (email, is_active)
VALUES
  ('a@example.com', true),
  ('b@example.com', false),
  ('c@example.com', false);

INSERT INTO customer_events (customer_id, event_type, created_at)
VALUES
  (1, 'signup', '2026-01-01 00:00:00+00'),
  (2, 'signup', '2026-01-01 00:00:00+00'),
  (2, 'support_ticket', '2026-02-01 00:00:00+00'),
  (3, 'signup', '2026-01-01 00:00:00+00');

-- Preview events for inactive customers
SELECT e.event_id, e.customer_id, e.event_type
FROM customer_events e
JOIN customers c ON c.customer_id = e.customer_id
WHERE c.is_active = false
ORDER BY e.event_id;

-- Delete events for inactive customers
DELETE FROM customer_events e
USING customers c
WHERE c.customer_id = e.customer_id
  AND c.is_active = false
RETURNING e.event_id, e.customer_id, e.event_type
ORDER BY e.event_id;

Expected output (preview):

 event_id | customer_id | event_type
----------+-------------+---------------
 2        | 2           | signup
 3        | 2           | support_ticket
 4        | 3           | signup
(3 rows)

Expected output (RETURNING):

 event_id | customer_id | event_type
----------+-------------+---------------
 2        | 2           | signup
 3        | 2           | support_ticket
 4        | 3           | signup
(3 rows)

---

Practical Use Cases

1) Retention cleanup for logs/events

DELETE FROM audit_events
WHERE created_at < now() - interval '90 days'
RETURNING event_id, created_at;

2) Clearing expired sessions for security

DELETE FROM sessions
WHERE expires_at < now()
RETURNING session_id, user_id;

3) Removing invalid imports after validation

DELETE FROM staging_orders
WHERE import_batch_id = 123
RETURNING order_id;

4) Compliance delete request (hard delete)

DELETE FROM users
WHERE user_id = 42
RETURNING user_id;

5) Remove duplicates (keep the newest)

WITH ranked AS (
  SELECT
    user_id,
    row_number() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
  FROM users
)
DELETE FROM users u
USING ranked r
WHERE u.user_id = r.user_id
  AND r.rn > 1
RETURNING u.user_id, u.email;

---

Common Mistakes & Troubleshooting

1) Missing WHERE deletes everything

Wrong SQL:

DELETE FROM sessions;

Bad outcome:

• Every row is removed.

Fix:

DELETE FROM sessions
WHERE expires_at < now()
RETURNING session_id;

---

2) Foreign key constraint blocks deletion

Wrong SQL:

DELETE FROM customers
WHERE customer_id = 10;

Typical error:

ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (customer_id)=(10) is still referenced from table "orders".

Fix options:

-- Option A: delete dependent rows first
DELETE FROM orders WHERE customer_id = 10;
DELETE FROM customers WHERE customer_id = 10 RETURNING customer_id;

-- Option B: design-time: use ON DELETE CASCADE where appropriate
-- (schema change, not done in an ad-hoc query)

---

3) Confusing DELETE with TRUNCATE

Wrong approach:

• Using DELETE FROM big_table; to clear a large staging table.

Bad outcome:

• Slower operation and more WAL than necessary.

Fix:

TRUNCATE TABLE staging_import;

---

4) Large delete causes lock pressure or bloat

Wrong approach:

DELETE FROM audit_events
WHERE created_at < now() - interval '365 days';

Bad outcome:

• Long-running transaction, heavy WAL, and increased bloat.

Fix (batch with a CTE):

WITH batch AS (
  SELECT event_id
  FROM audit_events
  WHERE created_at < now() - interval '365 days'
  ORDER BY event_id
  LIMIT 10000
)
DELETE FROM audit_events e
USING batch b
WHERE e.event_id = b.event_id
RETURNING e.event_id;

---

Debugging checklist:

1. Start a transaction (BEGIN;) for risky deletes.
2. Preview the exact target set with SELECT using the same predicates/joins.
3. Add RETURNING to see exactly what will be removed.
4. If you see FK errors, inspect dependent tables and cascading rules.
5. For performance issues, batch deletes and consider partitioning for retention.

---

Best Practices

• ✅ Use RETURNING for verification and audit logging. ❌ Avoid deletes that you cannot prove were correct.
• ✅ Prefer transactions while developing or when the scope is large. ❌ Avoid irreversible deletes in autocommit when you're unsure.
• ✅ Batch large deletes to reduce lock time and bloat. ❌ Avoid deleting millions of rows in a single transaction on hot tables.
• ✅ Consider soft delete (deleted_at) when recovery or audit is required. ❌ Avoid hard deletes when business rules require retention.
• ✅ For retention at scale, prefer partitioning and dropping partitions. ❌ Avoid row-by-row retention cleanup when table design can solve it.

---

Hands-On Practice

Use this setup for the exercises:

CREATE TABLE practice_messages (
  message_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint NOT NULL,
  body       text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

INSERT INTO practice_messages (user_id, body, created_at, deleted_at)
VALUES
  (10, 'hello', '2026-01-01 00:00:00+00', NULL),
  (10, 'draft', '2026-01-02 00:00:00+00', '2026-01-03 00:00:00+00'),
  (11, 'welcome', '2026-01-05 00:00:00+00', NULL);

Exercise 1 (Easy): Delete one message by id

Task: Delete message_id = 1 and return the deleted id.

-- Your SQL here

Solution:

DELETE FROM practice_messages
WHERE message_id = 1
RETURNING message_id;

---

Exercise 2 (Medium): Delete all soft-deleted rows

Task: Hard-delete rows where deleted_at IS NOT NULL and return the deleted ids.

-- Your SQL here

Solution:

DELETE FROM practice_messages
WHERE deleted_at IS NOT NULL
RETURNING message_id;

---

Exercise 3 (Advanced): Delete based on a join

Task: Create a blocklist table and delete all messages from blocked users using DELETE ... USING.

-- Your SQL here

Solution:

CREATE TABLE practice_blocked_users (
  user_id bigint PRIMARY KEY
);

INSERT INTO practice_blocked_users (user_id) VALUES (11);

DELETE FROM practice_messages m
USING practice_blocked_users b
WHERE b.user_id = m.user_id
RETURNING m.message_id, m.user_id;

---

Connection to Other Concepts

Concept	Why it matters
SELECT	preview targets before deletion and validate results after
WHERE	scopes the deletion to the intended rows
UPDATE	alternative: soft delete or status changes instead of removal
LIMIT	often used to sample/preview rows; for batching, use a CTE + LIMIT
Transactions	rollback safety for destructive operations
Foreign keys	can block deletes or cascade deletes depending on design

---

Visual Learning Diagram

flowchart TD
  A[SELECT + WHERE (Preview)] --> B[DELETE]
  B --> C[RETURNING (Verify)]
  B --> D[DELETE ... USING]
  B --> E[Transactions]
  E --> F[ROLLBACK / COMMIT]
  B --> G[FK Constraints]
  B --> H[TRUNCATE vs DELETE]
  B --> I[Batching + Retention]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G,H,I allNodes
  class B highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Deleting without previewing the target set	accidental data loss	run a preview SELECT and use RETURNING
Assuming delete frees disk immediately	surprise disk usage	understand vacuum/bloat; consider partitioning
Using DELETE to clear large staging tables	slow cleanup	prefer TRUNCATE when appropriate
Ignoring FK dependencies	blocked deletes or unintended cascades	design FK rules intentionally and test
Hard delete where audit/recovery is required	permanent loss	use soft deletes or archival tables

---

Quick Reference

DELETE FROM t WHERE id = 1 RETURNING id;
DELETE FROM t WHERE expires_at < now() RETURNING *;
DELETE FROM t USING other o WHERE o.id = t.other_id RETURNING t.*;
TRUNCATE TABLE t;

What's Next

• Previous: UPDATE - Review the previous lesson to reinforce context.
• Next: LIMIT - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.