PostgreSQL DELETE

🗑️ PostgreSQL DELETE Statement (Beginner → Advanced)

The DELETE statement in PostgreSQL is used to remove rows (records) from a table.
It is a DML command and is transaction-safe, meaning it can be rolled back.

1️⃣ What is DELETE in PostgreSQL?

DELETE removes one or more rows from a table based on a condition.

DELETE FROM table_name;

⚠️ Without WHERE, all rows are deleted (table structure remains).

2️⃣ Basic DELETE with WHERE ⭐ (Most Important)


✔ Deletes only the row with id = 5
✔ Safe and recommended usage

3️⃣ Delete Multiple Rows


✔ Deletes all students who failed

4️⃣ Delete All Rows from a Table ⚠️

DELETE FROM students;

✔ Removes all rows
❌ Slower than TRUNCATE
✔ Can be rolled back

5️⃣ DELETE with RETURNING 🔥 (PostgreSQL Special)

PostgreSQL allows you to see deleted rows.


✔ Shows deleted rows
✔ Very useful for auditing & debugging

6️⃣ DELETE Using a Subquery ⭐⭐


✔ Deletes based on another table’s data

7️⃣ DELETE with USING (JOIN Delete) 🔗


 

✔ PostgreSQL equivalent of DELETE JOIN
✔ Powerful and commonly used

8️⃣ DELETE with LIMIT ❌ (Not Supported)

DELETE FROM students LIMIT 5; -- ❌ Not allowed

Correct PostgreSQL Way:


 

9️⃣ DELETE and Transactions 🔁

BEGIN;
DELETE FROM students WHERE id = 10;
ROLLBACK; -- Undo delete

or

COMMIT; -- Permanently save

✔ PostgreSQL fully supports rollback for DELETE

🔟 DELETE vs TRUNCATE vs DROP ⭐ (Very Important)

Feature DELETE TRUNCATE DROP
Removes rows ✅ Yes ✅ Yes ❌ No
Removes table ❌ No ❌ No ✅ Yes
WHERE allowed ✅ Yes ❌ No ❌ No
Rollback ✅ Yes ❌ No ❌ No
Speed Slower Faster Fastest

✔ Use DELETE when conditions or rollback are required

1️⃣1️⃣ Performance Tips 🔧

  • Always use WHERE with indexed columns

  • Avoid deleting large datasets at once

  • Use batch deletes for big tables

  • Prefer TRUNCATE for full cleanup

1️⃣2️⃣ Common Mistakes ❌

❌ Forgetting WHERE clause
❌ Using DELETE instead of TRUNCATE
❌ Expecting LIMIT to work directly
❌ Not using transactions in production

📌 Interview Questions (Must Prepare)

  1. How does DELETE work in PostgreSQL?

  2. What is RETURNING in PostgreSQL?

  3. Difference between DELETE and TRUNCATE

  4. Can DELETE be rolled back?

  5. How to delete rows using another table?

  6. Why PostgreSQL does not support DELETE LIMIT?

🔥 Real-Life Use Cases

  • Removing inactive users

  • Cleaning old logs

  • GDPR data deletion

  • Fixing incorrect records

  • Maintenance jobs

✅ Summary

✔ DELETE removes rows, not the table
✔ Use WHERE to avoid full deletion
✔ PostgreSQL supports RETURNING
✔ Fully transactional (rollback supported)
✔ Use USING for join-based deletes
✔ Critical for safe database operations & interviews

You may also like...