DELETE in PostgreSQL

The DELETE statement removes rows from a table. It operates on data only — it does not drop columns, remove constraints, or change the table structure. Each deleted row is individually logged, which means DELETE supports ROLLBACK and row-level audit logging with RETURNING. To remove all rows from a large table quickly without per-row logging, TRUNCATE is faster.

Syntax

DELETE FROM table_name
WHERE condition;

With RETURNING to capture deleted row values:

DELETE FROM table_name
WHERE condition
RETURNING column1, column2;

Practical Example

Create a line_items table for an order management system and insert sample data:

CREATE TABLE orders (
  id      SERIAL PRIMARY KEY,
  ref     VARCHAR(20) NOT NULL,
  status  VARCHAR(20) NOT NULL DEFAULT 'open'
);

CREATE TABLE line_items (
  id         SERIAL PRIMARY KEY,
  order_id   INT NOT NULL REFERENCES orders(id),
  product    VARCHAR(100) NOT NULL,
  qty        INT NOT NULL,
  unit_price NUMERIC(10, 2) NOT NULL,
  cancelled  BOOLEAN NOT NULL DEFAULT false
);

INSERT INTO orders (ref, status) VALUES ('ORD-0001', 'open'), ('ORD-0002', 'open');

INSERT INTO line_items (order_id, product, qty, unit_price, cancelled)
VALUES
  (1, 'Wireless Keyboard', 2, 59.99, false),
  (1, 'Mouse Pad XL',      1,  9.99, true),
  (1, 'HDMI Cable 2m',     3, 12.00, false),
  (2, 'USB Hub 7-Port',    1, 34.99, true);

Delete a single cancelled line item:

DELETE FROM line_items
WHERE id = 2;

Output:

DELETE 1

Delete all cancelled line items across all orders:

DELETE FROM line_items
WHERE cancelled = true;

Output:

DELETE 1

Use RETURNING to capture the removed rows for an audit trail:

DELETE FROM line_items
WHERE order_id = 1
  AND qty < 2
RETURNING id, product, qty, unit_price;

 id |   product    | qty | unit_price
----+--------------+-----+------------
  3 | HDMI Cable 2m |   3 |      12.00

Verify the remaining rows:

SELECT id, order_id, product, qty
FROM line_items
ORDER BY id;

RETURNING Clause

RETURNING returns the values of the deleted rows before they are removed. This is useful for:

• Writing audit log entries without a second query.
• Collecting deleted IDs to clean up related data in application code.
• Confirming exactly which rows were removed in a conditional delete.

DELETE FROM line_items
WHERE order_id = 2
RETURNING id, product, unit_price AS removed_price;

The values in RETURNING reflect the row state at the moment of deletion — after any BEFORE DELETE trigger has run but before the row is physically removed.

Testing with Vela

Destructive operations like DELETE are irreversible once committed. Use Vela database branching to create a copy-on-write clone of your production database, run the DELETE on the branch, verify the affected row count and RETURNING output, then apply to production with confidence. This eliminates the risk of accidentally deleting the wrong rows in a live environment.

Production Tips

• Always run a SELECT with the same WHERE clause before executing a DELETE to confirm the row count and data that will be removed.
• Wrap deletes in an explicit transaction when they are part of a multi-step operation — issue ROLLBACK if a downstream step fails.
• Use RETURNING to capture deleted rows for audit logging or to pass removed data to application code.
• For large batch deletes, use a loop deleting in smaller chunks (e.g. LIMIT 1000) to avoid long lock contention on busy tables.
• When removing all rows from a table, TRUNCATE is far faster than an unfiltered DELETE because it does not log individual row deletions.
• Check foreign key constraints before deleting parent rows — use ON DELETE CASCADE or clean up child rows first to avoid constraint violations.