DELETE

DELETE removes rows from a table. It is the counterpart to INSERT. While INSERT adds data, DELETE removes it. Use it carefully — deleted data is gone for good.

Basic Delete

The basic syntax uses a WHERE clause to target specific rows:

DELETE FROM students
WHERE id = 15;

This removes the student with id 15. Always include a WHERE clause unless you truly want to delete every row.

Delete with Conditions

You can use any WHERE condition to target rows:

-- Delete inactive students
DELETE FROM students
WHERE status = 'inactive';

-- Delete old records
DELETE FROM logs
WHERE created_at < '2020-01-01';

-- Delete using multiple conditions
DELETE FROM students
WHERE enrollment_date < '2019-01-01'
AND status = 'graduated';

Delete All Rows

To remove every row from a table:

DELETE FROM students;

This is slow for large tables because it removes rows one at a time (to allow for triggers and foreign key checks). For faster removal of all rows, use TRUNCATE instead.

TRUNCATE vs DELETE

When you want to empty a table completely, TRUNCATE is faster:

TRUNCATE TABLE students;

The key differences:

• TRUNCATE is faster because it does not scan the table row by row
• TRUNCATE resets auto-incrementing counters back to the starting value
• TRUNCATE cannot be rolled back in some configurations
• DELETE fires row-level triggers; TRUNCATE fires statement-level triggers

Use DELETE when you need fine-grained control or want triggers to fire. Use TRUNCATE when you want to quickly empty a table.

Returning Deleted Rows

Like INSERT and UPDATE, DELETE supports RETURNING:

DELETE FROM students
WHERE status = 'inactive'
RETURNING *;

This shows you exactly which rows were deleted. Useful for logging or archiving before deletion.

Deleting with a Subquery

You can target rows based on data from other tables:

DELETE FROM students
WHERE id IN (
  SELECT student_id
  FROM enrollments
  WHERE course_id = 99
);

This deletes all students who are enrolled in course 99.

Safety Tips

Before running a DELETE:

• Run a SELECT with the same WHERE clause to see which rows will be affected
• Consider using BEGIN to start a transaction so you can roll back if something goes wrong
• Back up important data before deleting
• Remember that foreign key constraints might prevent deletion if other tables reference the data