PostgreSQL Lesson 21 – DELETE Data | Dataplexa

Deleting Data

Removing data from a database requires the same care and precision as updating it. The DELETE statement removes specific rows from a table — and like UPDATE, it will affect every row in the table if you forget the WHERE clause. This lesson covers every form of DELETE you will use in real projects, how to handle foreign key dependencies, how to use RETURNING to confirm what was deleted, and how DELETE compares to TRUNCATE for clearing large amounts of data.

Basic DELETE Syntax

A DELETE statement names the table and uses a WHERE clause to target specific rows. The WHERE clause works exactly like it does in SELECT — any valid boolean expression will filter the rows to delete.

-- Delete a specific customer by ID
DELETE FROM customers
WHERE id = 51;

-- Delete all orders with a specific status
DELETE FROM orders
WHERE status = 'cancelled';

-- Delete products that have been discontinued and have no stock
DELETE FROM products
WHERE is_active = FALSE AND stock_qty = 0;

-- Verify the deletion
SELECT COUNT(*) FROM orders WHERE status = 'cancelled';

DELETE 1
DELETE 14
DELETE 3

count
-------
0

The Critical Rule — Always Use WHERE

Just like UPDATE, a DELETE without a WHERE clause removes every single row in the table. PostgreSQL does not ask for confirmation — it just deletes everything instantly. The table structure stays, but all the data is gone. Always run a matching SELECT first to preview exactly which rows will be affected before committing to the DELETE.

-- DANGEROUS — deletes all rows in the customers table
DELETE FROM customers;

-- Safe approach: preview first with SELECT
SELECT id, first_name, email FROM customers WHERE state = 'NY';

-- Once confirmed, run the DELETE
DELETE FROM customers WHERE state = 'NY';

DELETE with RETURNING

The RETURNING clause works with DELETE just as it does with INSERT and UPDATE. It shows you the values of the rows that were just deleted — useful for logging, auditing, or passing deleted data to another operation without a second query.

-- Delete and return what was removed
DELETE FROM orders
WHERE status = 'cancelled'
RETURNING id, customer_id, total_amount, order_date;

-- Delete discontinued products and log them
DELETE FROM products
WHERE is_active = FALSE
RETURNING id, name, price, category;

-- Delete a customer and return their full record
DELETE FROM customers
WHERE id = 52
RETURNING *;

id | customer_id | total_amount | order_date
-----+-------------+--------------+------------
203 |          12 |        89.99 | 2024-05-14
217 |          34 |        45.00 | 2024-05-28

id |     name      | price |  category
----+--------------+-------+-----------
18 | Old Keyboard | 12.99 | Accessories
24 | Broken Stand | 8.50 | Office

DELETE with Subquery

You can use a subquery in the WHERE clause to delete rows based on conditions in another table. This is a clean way to remove data that depends on relationships without writing complex JOINs.

-- Delete all order items that belong to cancelled orders
DELETE FROM order_items
WHERE order_id IN (
  SELECT id FROM orders WHERE status = 'cancelled'
);

-- Delete customers who have never placed any order
DELETE FROM customers
WHERE id NOT IN (
  SELECT DISTINCT customer_id FROM orders
);

-- Delete orders older than 2 years with a delivered status
DELETE FROM orders
WHERE status = 'delivered'
  AND order_date < CURRENT_DATE - INTERVAL '2 years';

SELECT COUNT(*) FROM order_items;

DELETE 28
DELETE 5
DELETE 43

count
-------
322

DELETE with USING — Join-Based Deletion

PostgreSQL supports a USING clause in DELETE that lets you reference another table directly — similar to how UPDATE uses FROM. This is cleaner than a subquery when the deletion logic is based on a join condition.

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

-- Delete orders that belong to customers in a deactivated_customers table
DELETE FROM orders
USING deactivated_customers
WHERE orders.customer_id = deactivated_customers.id;

-- Delete employees in a specific department using a join
DELETE FROM employees
USING departments_to_remove
WHERE employees.department = departments_to_remove.name;

DELETE 16
DELETE 8
DELETE 3

Foreign Keys and DELETE — What Blocks You

When a table has rows that other tables reference via foreign keys, deleting those rows is blocked by default. PostgreSQL protects referential integrity — you cannot delete a customer who still has orders, or an order that still has order items. This protection is intentional and valuable.

-- Try to delete a customer that has orders — blocked
DELETE FROM customers WHERE id = 3;

-- Option 1: delete child rows first, then the parent
DELETE FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE customer_id = 3);
DELETE FROM orders WHERE customer_id = 3;
DELETE FROM customers WHERE id = 3;

-- Option 2: if the foreign key was defined with ON DELETE CASCADE,
-- deleting the customer automatically removes their orders and order items
-- (this behaviour is set at table creation time — see Lesson 11)

-- Check if a customer has orders before deleting
SELECT COUNT(*) FROM orders WHERE customer_id = 3;

ERROR: update or delete on table "customers" violates foreign key constraint
"orders_customer_id_fkey" on table "orders"
DETAIL: Key (id)=(3) is still referenced from table "orders".

DELETE 5  -- order_items removed
DELETE 2  -- orders removed
DELETE 1  -- customer removed

count
-------
     0

DELETE vs TRUNCATE vs DROP — Choosing the Right Tool

Feature	DELETE	TRUNCATE	DROP TABLE
Removes data	Yes — specific rows	Yes — all rows	Yes — all rows
Removes table structure	No	No	Yes
WHERE clause	Yes — filter rows	No — all or nothing	No
Speed on large tables	Slower — row by row	Instant	Instant
Fires row triggers	Yes	No	No
RETURNING support	Yes	No	No
Best for	Removing specific rows with conditions	Resetting entire table fast	Removing a table entirely

🧪 Practice Questions

Answer based on what you learned in this lesson.

1. What happens if you run DELETE FROM customers with no WHERE clause?

2. Which clause lets you see the deleted rows' values immediately after a DELETE without running a second query?

3. Which PostgreSQL-specific clause in DELETE lets you reference another table directly — similar to how UPDATE uses FROM?

4. A customer still has orders in the orders table. What must you do before you can delete that customer?

5. Which command fires row-level triggers for each deleted row — DELETE or TRUNCATE?

Next up: The SELECT statement in full — reading and querying your data every way possible.

← Previous Course Index Next →

PostgreSQL