DELETE in PostgreSQL

The DELETE statement is used to remove records from a table based on a specified condition. Unlike the DROP TABLE command, which removes the entire table, DELETE only removes specific rows of data.

Syntax

DELETE FROM table_nameWHERE condition;

• table_name: The name of the table from which rows will be deleted.
• WHERE: The condition that specifies which rows should be deleted. If omitted, all rows in the table will be deleted (use with caution!).

1. Basic Example

To delete a specific record from a table, use the WHERE clause to specify the condition.

DELETE FROM employeesWHERE id = 5;

This will delete the row from the employees table where the id is 5.

2. Deleting All Rows

If you want to delete all rows from a table, you can omit the WHERE clause.

DELETE FROM employees;

This will remove all rows from the employees table, but the table structure itself will remain.

3. Deleting Multiple Rows

To delete multiple rows based on a condition, you can use a more complex WHERE clause.

DELETE FROM employeesWHERE department = 'HR';

This will delete all employees in the HR department.

4. Using RETURNING Clause

PostgreSQL allows you to return the deleted rows using the RETURNING clause. This can be helpful if you want to capture and review the data that was deleted.

DELETE FROM employeesWHERE department = 'HR'RETURNING id, first_name, last_name;

This will delete all employees in the HR department and return their id, first_name, and last_name.

5. Deleting with Subquery

You can also delete rows based on a subquery, which allows more complex conditions.

DELETE FROM employeesWHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');

This will delete all employees whose department_id matches any id from the departments table where the location is New York.

6. Deleting with Joins

PostgreSQL doesn't directly support joins in a DELETE statement, but you can delete using a USING clause.

DELETE FROM employees eUSING departments dWHERE e.department_id = d.idAND d.location = 'New York';

This will delete employees who belong to departments located in New York.

7. Important Considerations

• Permanent data loss: Once the rows are deleted, they cannot be recovered unless you have a backup.
• Transaction control: If you're working with sensitive data or complex deletions, use transactions (BEGIN, COMMIT, ROLLBACK) to ensure that deletions are handled correctly and can be undone if needed.
• Foreign key constraints: If a table has foreign key constraints, attempting to delete rows that are referenced by other tables may result in an error, unless cascading is enabled (ON DELETE CASCADE).
• Performance: Deleting large amounts of data can be resource-intensive. Consider using batching or other optimization techniques for large tables.

Example with Transaction Control

If you need to delete data in multiple steps or tables, use transactions to ensure the process is atomic:

BEGIN;DELETE FROM employees WHERE department = 'HR';DELETE FROM departments WHERE location = 'New York';COMMIT;

If something goes wrong, you can rollback:

ROLLBACK;

This ensures that the deletion process can be undone if any part of the process fails.