1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>DELETE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-declare.html" title="DECLARE" /><link rel="next" href="sql-discard.html" title="DISCARD" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">DELETE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-declare.html" title="DECLARE">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-discard.html" title="DISCARD">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-DELETE"><div class="titlepage"></div><a id="id-1.9.3.100.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">DELETE</span></h2><p>DELETE — delete rows of a table</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
[ WITH [ RECURSIVE ] <em class="replaceable"><code>with_query</code></em> [, ...] ]
DELETE FROM [ ONLY ] <em class="replaceable"><code>table_name</code></em> [ * ] [ [ AS ] <em class="replaceable"><code>alias</code></em> ]
[ USING <em class="replaceable"><code>from_item</code></em> [, ...] ]
[ WHERE <em class="replaceable"><code>condition</code></em> | WHERE CURRENT OF <em class="replaceable"><code>cursor_name</code></em> ]
[ RETURNING * | <em class="replaceable"><code>output_expression</code></em> [ [ AS ] <em class="replaceable"><code>output_name</code></em> ] [, ...] ]
</pre></div><div class="refsect1" id="id-1.9.3.100.5"><h2>Description</h2><p>
<code class="command">DELETE</code> deletes rows that satisfy the
<code class="literal">WHERE</code> clause from the specified table. If the
<code class="literal">WHERE</code> clause is absent, the effect is to delete
all rows in the table. The result is a valid, but empty table.
</p><div class="tip"><h3 class="title">Tip</h3><p>
<a class="link" href="sql-truncate.html" title="TRUNCATE"><code class="command">TRUNCATE</code></a> provides a
faster mechanism to remove all rows from a table.
</p></div><p>
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 <code class="literal">USING</code> clause.
Which technique is more appropriate depends on the specific
circumstances.
</p><p>
The optional <code class="literal">RETURNING</code> clause causes <code class="command">DELETE</code>
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 <code class="literal">USING</code>, can be computed.
The syntax of the <code class="literal">RETURNING</code> list is identical to that of the
output list of <code class="command">SELECT</code>.
</p><p>
You must have the <code class="literal">DELETE</code> privilege on the table
to delete from it, as well as the <code class="literal">SELECT</code>
privilege for any table in the <code class="literal">USING</code> clause or
whose values are read in the <em class="replaceable"><code>condition</code></em>.
</p></div><div class="refsect1" id="id-1.9.3.100.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>with_query</code></em></span></dt><dd><p>
The <code class="literal">WITH</code> clause allows you to specify one or more
subqueries that can be referenced by name in the <code class="command">DELETE</code>
query. See <a class="xref" href="queries-with.html" title="7.8. WITH Queries (Common Table Expressions)">Section 7.8</a> and <a class="xref" href="sql-select.html" title="SELECT"><span class="refentrytitle">SELECT</span></a>
for details.
</p></dd><dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt><dd><p>
The name (optionally schema-qualified) of the table to delete rows
from. If <code class="literal">ONLY</code> is specified before the table name,
matching rows are deleted from the named table only. If
<code class="literal">ONLY</code> is not specified, matching rows are also deleted
from any tables inheriting from the named table. Optionally,
<code class="literal">*</code> can be specified after the table name to explicitly
indicate that descendant tables are included.
</p></dd><dt><span class="term"><em class="replaceable"><code>alias</code></em></span></dt><dd><p>
A substitute name for the target table. When an alias is
provided, it completely hides the actual name of the table. For
example, given <code class="literal">DELETE FROM foo AS f</code>, the remainder
of the <code class="command">DELETE</code> statement must refer to this
table as <code class="literal">f</code> not <code class="literal">foo</code>.
</p></dd><dt><span class="term"><em class="replaceable"><code>from_item</code></em></span></dt><dd><p>
A table expression allowing columns from other tables to appear
in the <code class="literal">WHERE</code> condition. This uses the same
syntax as the <a class="link" href="sql-select.html#SQL-FROM" title="FROM Clause"><code class="literal">FROM</code></a>
clause of a <code class="command">SELECT</code> statement; for example, an alias
for the table name can be specified. Do not repeat the target
table as a <em class="replaceable"><code>from_item</code></em>
unless you wish to set up a self-join (in which case it must appear
with an alias in the <em class="replaceable"><code>from_item</code></em>).
</p></dd><dt><span class="term"><em class="replaceable"><code>condition</code></em></span></dt><dd><p>
An expression that returns a value of type <code class="type">boolean</code>.
Only rows for which this expression returns <code class="literal">true</code>
will be deleted.
</p></dd><dt><span class="term"><em class="replaceable"><code>cursor_name</code></em></span></dt><dd><p>
The name of the cursor to use in a <code class="literal">WHERE CURRENT OF</code>
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 <code class="command">DELETE</code>'s target table.
Note that <code class="literal">WHERE CURRENT OF</code> cannot be
specified together with a Boolean condition. See
<a class="xref" href="sql-declare.html" title="DECLARE"><span class="refentrytitle">DECLARE</span></a>
for more information about using cursors with
<code class="literal">WHERE CURRENT OF</code>.
</p></dd><dt><span class="term"><em class="replaceable"><code>output_expression</code></em></span></dt><dd><p>
An expression to be computed and returned by the <code class="command">DELETE</code>
command after each row is deleted. The expression can use any
column names of the table named by <em class="replaceable"><code>table_name</code></em>
or table(s) listed in <code class="literal">USING</code>.
Write <code class="literal">*</code> to return all columns.
</p></dd><dt><span class="term"><em class="replaceable"><code>output_name</code></em></span></dt><dd><p>
A name to use for a returned column.
</p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.100.7"><h2>Outputs</h2><p>
On successful completion, a <code class="command">DELETE</code> command returns a command
tag of the form
</p><pre class="screen">
DELETE <em class="replaceable"><code>count</code></em>
</pre><p>
The <em class="replaceable"><code>count</code></em> is the number
of rows deleted. Note that the number may be less than the number of
rows that matched the <em class="replaceable"><code>condition</code></em> when deletes were
suppressed by a <code class="literal">BEFORE DELETE</code> trigger. If <em class="replaceable"><code>count</code></em> is 0, no rows were deleted by
the query (this is not considered an error).
</p><p>
If the <code class="command">DELETE</code> command contains a <code class="literal">RETURNING</code>
clause, the result will be similar to that of a <code class="command">SELECT</code>
statement containing the columns and values defined in the
<code class="literal">RETURNING</code> list, computed over the row(s) deleted by the
command.
</p></div><div class="refsect1" id="id-1.9.3.100.8"><h2>Notes</h2><p>
<span class="productname">PostgreSQL</span> lets you reference columns of
other tables in the <code class="literal">WHERE</code> condition by specifying the
other tables in the <code class="literal">USING</code> clause. For example,
to delete all films produced by a given producer, one can do:
</p><pre class="programlisting">
DELETE FROM films USING producers
WHERE producer_id = producers.id AND producers.name = 'foo';
</pre><p>
What is essentially happening here is a join between <code class="structname">films</code>
and <code class="structname">producers</code>, with all successfully joined
<code class="structname">films</code> rows being marked for deletion.
This syntax is not standard. A more standard way to do it is:
</p><pre class="programlisting">
DELETE FROM films
WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');
</pre><p>
In some cases the join style is easier to write or faster to
execute than the sub-select style.
</p></div><div class="refsect1" id="id-1.9.3.100.9"><h2>Examples</h2><p>
Delete all films but musicals:
</p><pre class="programlisting">
DELETE FROM films WHERE kind <> 'Musical';
</pre><p>
</p><p>
Clear the table <code class="literal">films</code>:
</p><pre class="programlisting">
DELETE FROM films;
</pre><p>
</p><p>
Delete completed tasks, returning full details of the deleted rows:
</p><pre class="programlisting">
DELETE FROM tasks WHERE status = 'DONE' RETURNING *;
</pre><p>
</p><p>
Delete the row of <code class="structname">tasks</code> on which the cursor
<code class="literal">c_tasks</code> is currently positioned:
</p><pre class="programlisting">
DELETE FROM tasks WHERE CURRENT OF c_tasks;
</pre></div><div class="refsect1" id="id-1.9.3.100.10"><h2>Compatibility</h2><p>
This command conforms to the <acronym class="acronym">SQL</acronym> standard, except
that the <code class="literal">USING</code> and <code class="literal">RETURNING</code> clauses
are <span class="productname">PostgreSQL</span> extensions, as is the ability
to use <code class="literal">WITH</code> with <code class="command">DELETE</code>.
</p></div><div class="refsect1" id="id-1.9.3.100.11"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-truncate.html" title="TRUNCATE"><span class="refentrytitle">TRUNCATE</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-declare.html" title="DECLARE">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-discard.html" title="DISCARD">Next</a></td></tr><tr><td width="40%" align="left" valign="top">DECLARE </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> DISCARD</td></tr></table></div></body></html>
|
