How to Delete Rows in PostgreSQL
Last updated August 26, 2026 · By the SaturnSQL team
Use DELETE FROM ... WHERE, with USING to join another table. RETURNING lists the deleted rows. Wrap risky deletes in a transaction so you can ROLLBACK after inspecting the result.
DDL and DML are both transactional in Postgres, so wrapping a delete in BEGIN gives you a real undo. RETURNING prints what went, you inspect it, then COMMIT or ROLLBACK. An open transaction holds locks, so decide quickly rather than walking away mid-statement.
BEGIN;
DELETE FROM orders WHERE status = 'cancelled' RETURNING id;
-- looks right?
COMMIT; -- or ROLLBACK;Delete with a join
USING is the Postgres spelling of a delete with a join, and as with UPDATE ... FROM the join condition goes in WHERE. Omitting it deletes the entire table. Turn it into a SELECT first: swap DELETE FROM orders o for SELECT o.* FROM orders o and run the identical USING and WHERE clauses.
DELETE FROM orders o
USING customers c
WHERE c.id = o.customer_id
AND c.is_test_account;Deleting a lot of rows
A DELETE marks rows dead but does not give the space back: the table stays the same size until autovacuum reclaims it, and the index entries linger too. Deleting millions of rows in one statement also holds a single long transaction, which blocks vacuum across the whole database. Delete in batches and let autovacuum keep up, or, when you are removing most of the table, write the survivors to a new table instead.
DELETE FROM orders
WHERE id IN (
SELECT id FROM orders WHERE created_at < now() - interval '2 years' LIMIT 10000
);Common errors
ERROR: update or delete on table "customers" violates foreign key constraint "orders_customer_fk" on table "orders" DETAIL: Key (id)=(7) is still referenced from table "orders".
Another table still points at the row you are deleting, and the foreign key was created without an ON DELETE action, so the default NO ACTION blocks it.
Delete or reassign the children first, or declare the cascade you actually want when creating the constraint.
DELETE FROM orders WHERE customer_id = 7;
DELETE FROM customers WHERE id = 7;
-- or, once, on the constraint itself:
ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id)
REFERENCES customers (id) ON DELETE CASCADE;ERROR: cannot truncate a table referenced in a foreign key constraint DETAIL: Table "orders" references "customers". HINT: Truncate table "orders" at the same time, or use TRUNCATE ... CASCADE.
You reached for TRUNCATE to empty the table quickly, but it cannot leave dangling references behind any more than DELETE can.
Truncate both tables in one statement, which the hint suggests, or use CASCADE if you are certain about what it will empty.
TRUNCATE TABLE customers, orders;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free