DELETE — delete rows of a table
[ WITH [ RECURSIVE ]with_query
[, ...] ] DELETE FROM [ ONLY ]table_name
[ * ] [ [ AS ]alias
] [ USINGfrom_item
[, ...] ] [ WHEREcondition
| WHERE CURRENT OFcursor_name
] [ RETURNING { * |output_expression
[ [ AS ]output_name
] } [, ...] ]
DELETE
deletes rows that satisfy the
WHERE
clause from the specified table. If the
WHERE
clause is absent, the effect is to delete
all rows in the table. The result is a valid, but empty table.
TRUNCATE
provides a
faster mechanism to remove all rows from a table.
There are two ways to delete rows in a table using information
contained in other tables in the database: using sub-selects, or
specifying additional tables in the USING
clause.
Which technique is more appropriate depends on the specific
circumstances.
The optional RETURNING
clause causes DELETE
to compute and return value(s) based on each row actually deleted.
Any expression using the table's columns, and/or columns of other
tables mentioned in USING
, can be computed.
The syntax of the RETURNING
list is identical to that of the
output list of SELECT
.
You must have the DELETE
privilege on the table
to delete from it, as well as the SELECT
privilege for any table in the USING
clause or
whose values are read in the condition
.
with_query
The WITH
clause allows you to specify one or more
subqueries that can be referenced by name in the DELETE
query. See Section 7.8 and SELECT
for details.
table_name
The name (optionally schema-qualified) of the table to delete rows
from. If ONLY
is specified before the table name,
matching rows are deleted from the named table only. If
ONLY
is not specified, matching rows are also deleted
from any tables inheriting from the named table. Optionally,
*
can be specified after the table name to explicitly
indicate that descendant tables are included.
alias
A substitute name for the target table. When an alias is
provided, it completely hides the actual name of the table. For
example, given DELETE FROM foo AS f
, the remainder
of the DELETE
statement must refer to this
table as f
not foo
.
from_item
A table expression allowing columns from other tables to appear
in the WHERE
condition. This uses the same
syntax as the FROM
clause of a SELECT
statement; for example, an alias
for the table name can be specified. Do not repeat the target
table as a from_item
unless you wish to set up a self-join (in which case it must appear
with an alias in the from_item
).
condition
An expression that returns a value of type boolean
.
Only rows for which this expression returns true
will be deleted.
cursor_name
The name of the cursor to use in a WHERE CURRENT OF
condition. The row to be deleted is the one most recently fetched
from this cursor. The cursor must be a non-grouping
query on the DELETE
's target table.
Note that WHERE CURRENT OF
cannot be
specified together with a Boolean condition. See
DECLARE
for more information about using cursors with
WHERE CURRENT OF
.
output_expression
An expression to be computed and returned by the DELETE
command after each row is deleted. The expression can use any
column names of the table named by table_name
or table(s) listed in USING
.
Write *
to return all columns.
output_name
A name to use for a returned column.
On successful completion, a DELETE
command returns a command
tag of the form
DELETE count
The count
is the number
of rows deleted. Note that the number may be less than the number of
rows that matched the condition
when deletes were
suppressed by a BEFORE DELETE
trigger. If count
is 0, no rows were deleted by
the query (this is not considered an error).
If the DELETE
command contains a RETURNING
clause, the result will be similar to that of a SELECT
statement containing the columns and values defined in the
RETURNING
list, computed over the row(s) deleted by the
command.
PostgreSQL lets you reference columns of
other tables in the WHERE
condition by specifying the
other tables in the USING
clause. For example,
to delete all films produced by a given producer, one can do:
DELETE FROM films USING producers WHERE producer_id = producers.id AND producers.name = 'foo';
What is essentially happening here is a join between films
and producers
, with all successfully joined
films
rows being marked for deletion.
This syntax is not standard. A more standard way to do it is:
DELETE FROM films WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');
In some cases the join style is easier to write or faster to execute than the sub-select style.
Delete all films but musicals:
DELETE FROM films WHERE kind <> 'Musical';
Clear the table films
:
DELETE FROM films;
Delete completed tasks, returning full details of the deleted rows:
DELETE FROM tasks WHERE status = 'DONE' RETURNING *;
Delete the row of tasks
on which the cursor
c_tasks
is currently positioned:
DELETE FROM tasks WHERE CURRENT OF c_tasks;
While there is no LIMIT
clause
for DELETE
, it is possible to get a similar effect
using the same method described in the
documentation of UPDATE
:
WITH delete_batch AS ( SELECT l.ctid FROM user_logs AS l WHERE l.status = 'archived' ORDER BY l.creation_date FOR UPDATE LIMIT 10000 ) DELETE FROM user_logs AS dl USING delete_batch AS del WHERE dl.ctid = del.ctid;
This command conforms to the SQL standard, except
that the USING
and RETURNING
clauses
are PostgreSQL extensions, as is the ability
to use WITH
with DELETE
.