DELETE | PostgreSQL Tutorial

The DELETE statement removes rows from a table. It operates only on data — it does not drop columns, change constraints, or modify the table definition. To remove all rows very quickly without logging individual deletes, consider TRUNCATE instead.

Basic DELETE syntax

Specify the table after DELETE FROM and filter rows with WHERE:

DELETE FROM todos
WHERE id = 1;

Output:

DELETE 1

The command tag returns the number of rows deleted. If the WHERE condition matches no rows, you get DELETE 0 — not an error.

To delete multiple rows matching a condition:

DELETE FROM todos
WHERE completed = true;

Without a WHERE clause, every row in the table is removed:

DELETE FROM todos;

Returning deleted rows

The RETURNING clause gives you back the values of the deleted rows — useful for audit logging or confirming what was removed:

DELETE FROM todos
WHERE id = 2
RETURNING *;

Output:

 id |              title              | completed
----+---------------------------------+-----------
  2 | Practice writing SELECT queries | f
(1 row)

You can also return a specific column, for example to collect all deleted IDs:

DELETE FROM todos
WHERE completed = true
RETURNING id, title;

Practical tips

Always verify your WHERE clause with a SELECT before running the delete — confirm the row count matches your expectation.
Wrap deletes in a transaction when they are part of a multi-step operation so you can roll back if something fails.
To delete rows based on data in another table, use DELETE ... USING (the PostgreSQL delete-join syntax) rather than a subquery when possible.
For large batch deletes, delete in smaller chunks to avoid long lock contention on the table.
Use TRUNCATE when you need to remove all rows — it is much faster than an unfiltered DELETE for large tables.

Reference: PostgreSQL documentation — DELETE.

Frequently Asked Questions

What is the difference between DELETE and TRUNCATE in PostgreSQL?

DELETE removes rows one at a time and logs each deletion, supports a WHERE clause, and fires triggers. TRUNCATE removes all rows in a single operation without logging individual rows, is much faster for large tables, but cannot be filtered with WHERE.

What does DELETE 0 mean?

DELETE 0 means the WHERE clause matched no rows, so nothing was deleted. This is not an error.

Can I undo a DELETE in PostgreSQL?

Yes, if the DELETE was run inside an explicit transaction that has not yet been committed. Issue ROLLBACK to undo it. Once committed, the rows are gone and can only be recovered from a backup.

How do I delete rows based on data from another table?

Use the USING clause: DELETE FROM table1 USING table2 WHERE table1.id = table2.id; This is the PostgreSQL equivalent of a DELETE JOIN. Alternatively, use a subquery with WHERE id IN (SELECT ...).

Does DELETE remove the table structure?

No. DELETE removes rows but leaves the table, its columns, indexes, and constraints intact. To remove the table structure, use DROP TABLE. To remove all rows and reset sequences, use TRUNCATE.