How to DELETE Rows in PostgreSQL

How do I delete rows in PostgreSQL without harming related data?

DELETE permanently removes selected rows from a PostgreSQL table.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does DELETE do in PostgreSQL?

DELETE removes rows that meet a given WHERE condition. Without WHERE, it wipes the entire table. RETURNING can output affected rows for verification.

How do I delete a single row?

Use a primary-key filter. Example: DELETE FROM Customers WHERE id = 42 RETURNING *; confirms the exact customer removed.

How can I delete rows with JOIN logic?

Combine USING to reference other tables.Example: remove customers with no orders: DELETE FROM Customers c USING Orders o WHERE c.id = o.customer_id AND o.id IS NULL;

When should I use ONLY?

ONLY restricts deletion to the specified table, skipping inheriting tables.Syntax: DELETE FROM ONLY Products WHERE stock = 0;

Can I preview rows before deleting?

Use a SELECT with the same WHERE clause or append RETURNING to capture deleted data for audit logs.

How do I delete in batches for large tables?

Wrap DELETE in a loop with LIMIT and COMMIT per batch to avoid long locks: DELETE FROM Orders WHERE order_date < NOW() - INTERVAL '2 years' LIMIT 10000;

What is the safest way to delete all rows?

TRUNCATE is faster but bypasses triggers.Prefer DELETE when triggers or RETURNING data are needed.

Why is DELETE slower than TRUNCATE?

DELETE logs each row and fires triggers. TRUNCATE logs at the table level. For mass purges, TRUNCATE followed by VACUUM is quicker.

Why How to DELETE Rows in PostgreSQL is important

How to DELETE Rows in PostgreSQL Example Usage


-- Remove all abandoned carts (orders with no items)
DELETE FROM Orders o
USING (
  SELECT order_id FROM OrderItems GROUP BY order_id HAVING COUNT(*) = 0
) AS empty_cart
WHERE o.id = empty_cart.order_id
RETURNING o.id, o.customer_id;

How to DELETE Rows in PostgreSQL Syntax


DELETE [ONLY] FROM table_name [*] [AS alias]
       [USING using_list]
       [WHERE condition]
       [RETURNING * | output_expression[, ...]];

-- Ecommerce examples
-- 1. Delete a customer by ID
DELETE FROM Customers WHERE id = 42;

-- 2. Delete all out-of-stock products
DELETE FROM Products WHERE stock = 0 RETURNING id, name;

-- 3. Delete orders older than a year
DELETE FROM Orders WHERE order_date < CURRENT_DATE - INTERVAL '1 year';

-- 4. Delete order items via JOIN
DELETE FROM OrderItems oi USING Orders o
WHERE oi.order_id = o.id AND o.total_amount = 0;

Common Mistakes

Omitting the WHERE clause accidentally removes every row. Always add WHERE or run a preceding SELECT to verify the filter.
Forgetting RETURNING when you need audit data. Fix by appending RETURNING * or specific columns to capture deleted rows.