PostgreSQL DELETE Rows of Table – Examples

PostgreSQL DELETE Query

The PostgreSQL DELETE statement removes one or more rows from a table. A WHERE clause can target specific rows by using conditions with operators such as =, LIKE, IN, NOT IN, and comparison operators.

The basic syntax of a PostgreSQL DELETE query is:

 DELETE FROM table_name
	WHERE condition;

The WHERE clause is optional. When it is present, PostgreSQL deletes only the rows that satisfy the condition.

If you omit the WHERE clause, PostgreSQL deletes every row in the table. The table itself, including its columns, constraints, and indexes, remains available.

Delete One Row from a PostgreSQL Table

To delete one row, use a condition that uniquely identifies it. A primary-key column is usually the safest choice because its value is unique for each row.

Consider the following students table.

The following DELETE query removes the row where id=3.

 DELETE FROM students
	WHERE id=3;

After running the query, check the table contents to confirm that the intended row was removed.

Delete Multiple Rows with a WHERE Condition

A DELETE statement can remove several rows when more than one row matches its condition. For example, the following query deletes students whose attendance is below 75.

DELETE FROM students
WHERE attendance < 75;

Before executing a multi-row deletion, run a SELECT statement with the same condition. This lets you inspect the affected rows first.

SELECT *
FROM students
WHERE attendance < 75;

Delete Rows with IN, LIKE, and Date Conditions

You can use standard PostgreSQL expressions in the WHERE clause. The following examples show common ways to select rows for deletion.

Delete rows whose IDs appear in a list:

DELETE FROM students
WHERE id IN (4, 7, 10);

Delete rows whose names begin with Test:

DELETE FROM students
WHERE name LIKE 'Test%';

Delete rows created before a specified date:

DELETE FROM students
WHERE created_at < DATE '2024-01-01';

Return Deleted Rows with PostgreSQL RETURNING

PostgreSQL supports a RETURNING clause that displays values from the rows removed by a DELETE statement. This is useful when an application needs the deleted row data or when you want immediate confirmation of what was removed.

DELETE FROM students
WHERE id = 3
RETURNING id, name;

To return every column from each deleted row, use RETURNING *.

DELETE FROM students
WHERE attendance < 75
RETURNING *;

Delete Rows Using Values from Another Table

The PostgreSQL-specific USING clause allows a DELETE query to reference another table. In the following example, students are deleted when their IDs appear in an inactive_students table.

DELETE FROM students AS s
USING inactive_students AS i
WHERE s.id = i.student_id;

Conditions involving the target table and the additional table are written in the WHERE clause.

Delete All Rows from a PostgreSQL Table

Now we will delete all the rows of the students table. The following query has no WHERE clause, so every row is selected for deletion. Exercise caution before running it.

 DELETE FROM students;

Let us check the contents of the table after executing the statement.

All rows have been deleted, but the students table still exists.

PostgreSQL DELETE Compared with TRUNCATE and DROP

DELETE, TRUNCATE, and DROP serve different purposes:

• DELETE removes selected rows or all rows and can use a WHERE clause.
• TRUNCATE quickly removes all rows from a table but does not accept a WHERE clause.
• DROP TABLE removes the table definition and its data.

Use DELETE when rows must be filtered, deleted-row values must be returned, or row-level delete behavior is required.

Safely Test a PostgreSQL DELETE in a Transaction

For an important deletion, run the statement inside a transaction. You can inspect the result and then either keep the change with COMMIT or undo it with ROLLBACK.

BEGIN;

DELETE FROM students
WHERE id = 3
RETURNING *;

ROLLBACK;

Replace ROLLBACK with COMMIT only after confirming that the correct rows were deleted.

Common PostgreSQL DELETE Mistakes

• Omitting the WHERE clause: This deletes every row in the target table.
• Using a condition that matches more rows than expected: Test the condition with SELECT before deleting.
• Comparing with NULL using an equals sign: Use IS NULL or IS NOT NULL instead of = NULL.
• Ignoring foreign-key constraints: A deletion can fail when another table references the row, unless the relationship permits cascading or the dependent rows are handled first.
• Assuming deleted data is automatically recoverable: Once a deletion is committed, recovery generally requires a backup, replication setup, or another recovery mechanism.

PostgreSQL DELETE Query FAQs

How do I delete a specific row in PostgreSQL?

Use a DELETE FROM statement with a WHERE condition that uniquely identifies the row, such as its primary-key value.

What happens when WHERE is omitted from PostgreSQL DELETE?

PostgreSQL deletes all rows from the table. It does not delete the table structure.

Can PostgreSQL return the rows deleted by a query?

Yes. Add a RETURNING clause, such as RETURNING *, to receive values from the deleted rows.

How do I delete rows where a column is NULL?

Use an IS NULL condition. For example, DELETE FROM students WHERE email IS NULL;.

Can a PostgreSQL DELETE query be rolled back?

Yes, if the deletion is still inside an uncommitted transaction. Run ROLLBACK before committing the transaction.

PostgreSQL DELETE Tutorial Summary

In this PostgreSQL Tutorial, we used the PostgreSQL DELETE statement to remove one row, multiple matching rows, or all rows from a table. We also used RETURNING, referenced another table with USING, and tested a deletion safely inside a transaction.