Prev Next

PostgreSQL DELETE

The DELETE statement in PostgreSQL is used to remove rows from a table. You can delete specific rows that match a condition or remove all rows in the table.

Key Topics

1. Basic Syntax

DELETE FROM table_name;

This removes all rows from the specified table if no WHERE clause is provided.

2. Using WHERE

To target specific rows, include a WHERE clause:

DELETE FROM employees
WHERE emp_id = 101;

This removes only the row(s) where emp_id is 101.

3. Using RETURNING

You can retrieve information about deleted rows using RETURNING:

DELETE FROM employees
WHERE department = 'Temporary'
RETURNING emp_id, first_name, last_name;

Code Explanation: This deletes all employees in the 'Temporary' department and returns their IDs and names in one operation.

Best Practices

  • Use transactions or test queries before deleting rows in production to avoid accidental data loss.
  • Combine WHERE with a precise condition to target specific rows.
  • Use RETURNING to confirm which rows were deleted, especially in complex operations.

Key Takeaways

  • DELETE can remove specific rows or an entire table's data.
  • A WHERE clause is essential to avoid unintended mass deletions.
  • RETURNING helps track deleted row information without running a separate SELECT.
Prev Next