References
DML
Delete

Reference: Delete Statement

With delete, you can delete rows of a table.

Usage example:

delete from orders
where status = 'canceled';

We recommend always specifying a where clause for delete statements. If you want to delete all rows of a table, consider using truncate instead.

Returning Deleted Rows

Delete queries are similar in their expressiveness to read-only select queries. You can use arbitrary CTEs in a with clause, and you can specify additional tables to be joined in a using clause. This can be combined with a returning clause to get data out of the database exactly once, while simultaneously enriching it with related data.

The following example extracts pending notifications from a database table, and returns them combined with their corresponding channel information.

delete from pending_notifications n
using channels c
where n.receiver_id = $1
  and n.channel_id = c.id
returning *;

Scans in the returning clause read the database after the delete is completed. That is why the exists statement in the returning clause does not match the deleted row with itself in the following example.

delete from users
where users.user_id = 42
returning users.user_id, exists (select * from users where users.user_id=42);

user_id | ?column? 
--------+----------
  42     | f

Permissions

To delete from a table, you need the DELETE privilege on it, and USAGE on its schema. If the statement reads columns of the table, in WHERE or RETURNING, you also need the SELECT privilege. Tables in USING require the SELECT privilege.

PostgreSQL Differences

  • RETURNING requires the SELECT privilege on the table, even if it does not reference a column, such as RETURNING 1. PostgreSQL only requires SELECT on the columns that RETURNING references.
  • WHERE CURRENT OF is not supported.
CopyInsert