PostgreSQL DELETE

DELETE Components

WHERE id = 10

RETURNING *

USING other_table

-- Avoid: DELETE FROM table;

🔹 Basic DELETE Statement

The basic DELETE syntax removes rows matching your WHERE condition. Without a WHERE clause, all rows are deleted. Always double-check your condition before executing. Use SELECT with the same WHERE clause first to preview which rows will be deleted for safety.

-- Delete a single row by ID
DELETE FROM customers WHERE id = 100;

-- Delete multiple rows with condition
DELETE FROM orders WHERE status = 'cancelled';

-- Delete rows older than a date
DELETE FROM logs WHERE created_at < '2023-01-01';

DELETE 3
-- Three rows were deleted

🔹 DELETE with Multiple Conditions

Combine multiple conditions using AND and OR operators to target specific rows. This allows precise control over which data gets deleted. Complex conditions help ensure you only remove the exact records you intend to, protecting your data from accidental deletion mistakes.

-- Delete with AND condition
DELETE FROM products 
WHERE stock = 0 AND discontinued = true;

-- Delete with OR condition
DELETE FROM users 
WHERE status = 'inactive' OR last_login < '2022-01-01';

-- Delete with complex conditions
DELETE FROM orders 
WHERE (status = 'pending' AND created_at < NOW() - INTERVAL '30 days')
   OR (status = 'cancelled');

DELETE 15
-- Fifteen rows matched the conditions and were deleted

🔹 DELETE with RETURNING

The RETURNING clause returns data from deleted rows, allowing you to see what was removed. You can return all columns with asterisk or specific columns. This is useful for logging, auditing, or confirming the correct data was deleted before committing the transaction permanently.

-- Return all columns of deleted rows
DELETE FROM products 
WHERE stock = 0 
RETURNING *;

-- Return specific columns
DELETE FROM users 
WHERE status = 'inactive' 
RETURNING id, email, deleted_at;

-- Use returned data in application
DELETE FROM orders 
WHERE status = 'cancelled' 
RETURNING id, customer_id, total_amount;

id | email              | deleted_at
---+--------------------+------------
5  | [email protected]    | 2024-01-15
8  | [email protected] | 2024-01-15

🔹 DELETE with Subquery

Subqueries in DELETE statements allow you to remove rows based on data from other tables. The subquery runs first and returns values used in the WHERE condition. This technique enables complex deletion logic without requiring explicit joins in your DELETE statement structure.

-- Delete based on subquery
DELETE FROM order_items 
WHERE order_id IN (
    SELECT id FROM orders WHERE status = 'cancelled'
);

-- Delete using NOT IN
DELETE FROM products 
WHERE category_id NOT IN (
    SELECT id FROM categories WHERE active = true
);

-- Delete with correlated subquery
DELETE FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM departments d WHERE d.id = e.department_id
);

DELETE 42
-- Deleted items from cancelled orders

🔹 DELETE with USING Clause

The USING clause joins other tables in DELETE statements, enabling deletion based on related data. This PostgreSQL-specific feature simplifies queries that would otherwise require subqueries. It's particularly useful when you need to delete rows based on conditions involving multiple tables in your database schema.

-- Delete using join
DELETE FROM order_items
USING orders
WHERE order_items.order_id = orders.id
  AND orders.status = 'cancelled';

-- Delete with multiple table joins
DELETE FROM products
USING categories, suppliers
WHERE products.category_id = categories.id
  AND products.supplier_id = suppliers.id
  AND categories.active = false
  AND suppliers.status = 'inactive';

DELETE 28
-- Deleted products from inactive categories and suppliers

🔹 Safety Best Practices

Always test DELETE statements carefully to prevent data loss. Use transactions to rollback if something goes wrong. Create backups before bulk deletions. Start with SELECT to verify which rows match your conditions, then change SELECT to DELETE once confirmed the results are correct.

-- Safe deletion pattern
BEGIN;
    -- First, preview what will be deleted
    SELECT * FROM users WHERE status = 'inactive';
    
    -- If correct, execute delete
    DELETE FROM users WHERE status = 'inactive' RETURNING *;
    
    -- Review results, then commit or rollback
COMMIT; -- or ROLLBACK;