PostgreSQL DELETE Statement

The DELETE statement in PostgreSQL is used to remove one or more rows from a table. Just like UPDATE, it is extremely powerful and must be used carefully. Without a WHERE clause, the entire table will be cleared.

1. Basic DELETE Syntax

DELETE FROM table_name
WHERE condition;

- table_name → the table you want to delete rows from - WHERE → defines which rows should be deleted - Omitting WHERE will delete all rows

2. Example: Deleting a Single Row

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50),
  salary NUMERIC(10,2)
);

-- Delete employee with ID 1
DELETE FROM employees
WHERE id = 1;

3. Deleting Multiple Rows

You can delete multiple rows if they match the condition.

-- Delete all employees from HR department
DELETE FROM employees
WHERE department = 'HR';

4. Deleting All Rows in a Table

If you want to clear the entire table but keep its structure, you can omit the WHERE clause.

-- Delete all rows
DELETE FROM employees;

-- Alternative (faster for large tables)
TRUNCATE TABLE employees;

- DELETE removes rows one by one and can be rolled back. - TRUNCATE is faster but cannot be rolled back in the same way. It also resets auto-increment counters.

5. DELETE with Subquery

You can use subqueries to delete rows based on data from another table.

CREATE TABLE retired_employees (
  id INT,
  name VARCHAR(100)
);

-- Delete employees that appear in retired_employees
DELETE FROM employees
WHERE id IN (SELECT id FROM retired_employees);

6. DELETE with RETURNING Clause

PostgreSQL allows you to return deleted rows using the RETURNING clause.

-- Delete employees earning less than 30000
DELETE FROM employees
WHERE salary < 30000
RETURNING id, name, salary;

This is useful when you want to check which rows were deleted immediately.

7. DELETE with JOIN (Using USING Clause)

You can delete rows based on conditions from another table using the USING clause.

CREATE TABLE blacklist (
  email VARCHAR(100)
);

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(100),
  name VARCHAR(100)
);

-- Delete users whose email exists in blacklist
DELETE FROM users
USING blacklist
WHERE users.email = blacklist.email;

8. Difference Between DELETE and TRUNCATE

Best Practices

• Always use a WHERE clause unless you want to clear all rows.
• Use RETURNING to verify deleted rows.
• For very large deletions, prefer TRUNCATE if you don’t need rollback.
• Wrap DELETE in transactions when deleting critical data.

Conclusion

The DELETE statement is powerful for removing unwanted data, and PostgreSQL provides extra features like RETURNING andUSING to make deletions safer and more efficient. In the next tutorial, we will study the WHERE clause, which is essential for filtering data in PostgreSQL queries.