1<!--
2doc/src/sgml/ref/delete.sgml
3PostgreSQL documentation
4-->
5
6<refentry id="sql-delete">
7 <indexterm zone="sql-delete">
8  <primary>DELETE</primary>
9 </indexterm>
10
11 <refmeta>
12  <refentrytitle>DELETE</refentrytitle>
13  <manvolnum>7</manvolnum>
14  <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18  <refname>DELETE</refname>
19  <refpurpose>delete rows of a table</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23<synopsis>
24[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
25DELETE FROM [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
26    [ USING <replaceable class="parameter">from_item</replaceable> [, ...] ]
27    [ WHERE <replaceable class="parameter">condition</replaceable> | WHERE CURRENT OF <replaceable class="parameter">cursor_name</replaceable> ]
28    [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
29</synopsis>
30 </refsynopsisdiv>
31
32 <refsect1>
33  <title>Description</title>
34
35  <para>
36   <command>DELETE</command> deletes rows that satisfy the
37   <literal>WHERE</literal> clause from the specified table.  If the
38   <literal>WHERE</literal> clause is absent, the effect is to delete
39   all rows in the table.  The result is a valid, but empty table.
40  </para>
41
42   <tip>
43    <para>
44     <xref linkend="sql-truncate"/> provides a
45     faster mechanism to remove all rows from a table.
46    </para>
47   </tip>
48
49  <para>
50   There are two ways to delete rows in a table using information
51   contained in other tables in the database: using sub-selects, or
52   specifying additional tables in the <literal>USING</literal> clause.
53   Which technique is more appropriate depends on the specific
54   circumstances.
55  </para>
56
57  <para>
58   The optional <literal>RETURNING</literal> clause causes <command>DELETE</command>
59   to compute and return value(s) based on each row actually deleted.
60   Any expression using the table's columns, and/or columns of other
61   tables mentioned in <literal>USING</literal>, can be computed.
62   The syntax of the <literal>RETURNING</literal> list is identical to that of the
63   output list of <command>SELECT</command>.
64  </para>
65
66  <para>
67   You must have the <literal>DELETE</literal> privilege on the table
68   to delete from it, as well as the <literal>SELECT</literal>
69   privilege for any table in the <literal>USING</literal> clause or
70   whose values are read in the <replaceable
71   class="parameter">condition</replaceable>.
72  </para>
73 </refsect1>
74
75 <refsect1>
76  <title>Parameters</title>
77
78  <variablelist>
79   <varlistentry>
80    <term><replaceable class="parameter">with_query</replaceable></term>
81    <listitem>
82     <para>
83      The <literal>WITH</literal> clause allows you to specify one or more
84      subqueries that can be referenced by name in the <command>DELETE</command>
85      query. See <xref linkend="queries-with"/> and <xref linkend="sql-select"/>
86      for details.
87     </para>
88    </listitem>
89   </varlistentry>
90
91   <varlistentry>
92    <term><replaceable class="parameter">table_name</replaceable></term>
93    <listitem>
94     <para>
95      The name (optionally schema-qualified) of the table to delete rows
96      from.  If <literal>ONLY</literal> is specified before the table name,
97      matching rows are deleted from the named table only.  If
98      <literal>ONLY</literal> is not specified, matching rows are also deleted
99      from any tables inheriting from the named table.  Optionally,
100      <literal>*</literal> can be specified after the table name to explicitly
101      indicate that descendant tables are included.
102     </para>
103    </listitem>
104   </varlistentry>
105
106   <varlistentry>
107    <term><replaceable class="parameter">alias</replaceable></term>
108    <listitem>
109     <para>
110      A substitute name for the target table. When an alias is
111      provided, it completely hides the actual name of the table.  For
112      example, given <literal>DELETE FROM foo AS f</literal>, the remainder
113      of the <command>DELETE</command> statement must refer to this
114      table as <literal>f</literal> not <literal>foo</literal>.
115     </para>
116    </listitem>
117   </varlistentry>
118
119   <varlistentry>
120    <term><replaceable class="parameter">from_item</replaceable></term>
121    <listitem>
122     <para>
123      A table expression allowing columns from other tables to appear
124      in the <literal>WHERE</literal> condition.  This uses the same
125      syntax as the <link linkend="sql-from"><literal>FROM</literal></link>
126      clause of a <command>SELECT</command> statement; for example, an alias
127      for the table name can be specified.  Do not repeat the target
128      table as a <replaceable class="parameter">from_item</replaceable>
129      unless you wish to set up a self-join (in which case it must appear
130      with an alias in the <replaceable>from_item</replaceable>).
131     </para>
132    </listitem>
133   </varlistentry>
134
135   <varlistentry>
136    <term><replaceable class="parameter">condition</replaceable></term>
137    <listitem>
138     <para>
139      An expression that returns a value of type <type>boolean</type>.
140      Only rows for which this expression returns <literal>true</literal>
141      will be deleted.
142     </para>
143    </listitem>
144   </varlistentry>
145
146   <varlistentry>
147    <term><replaceable class="parameter">cursor_name</replaceable></term>
148    <listitem>
149     <para>
150      The name of the cursor to use in a <literal>WHERE CURRENT OF</literal>
151      condition.  The row to be deleted is the one most recently fetched
152      from this cursor.  The cursor must be a non-grouping
153      query on the <command>DELETE</command>'s target table.
154      Note that <literal>WHERE CURRENT OF</literal> cannot be
155      specified together with a Boolean condition.  See
156      <xref linkend="sql-declare"/>
157      for more information about using cursors with
158      <literal>WHERE CURRENT OF</literal>.
159     </para>
160    </listitem>
161   </varlistentry>
162
163   <varlistentry>
164    <term><replaceable class="parameter">output_expression</replaceable></term>
165    <listitem>
166     <para>
167      An expression to be computed and returned by the <command>DELETE</command>
168      command after each row is deleted.  The expression can use any
169      column names of the table named by <replaceable class="parameter">table_name</replaceable>
170      or table(s) listed in <literal>USING</literal>.
171      Write <literal>*</literal> to return all columns.
172     </para>
173    </listitem>
174   </varlistentry>
175
176   <varlistentry>
177    <term><replaceable class="parameter">output_name</replaceable></term>
178    <listitem>
179     <para>
180      A name to use for a returned column.
181     </para>
182    </listitem>
183   </varlistentry>
184  </variablelist>
185 </refsect1>
186
187 <refsect1>
188  <title>Outputs</title>
189
190  <para>
191   On successful completion, a <command>DELETE</command> command returns a command
192   tag of the form
193<screen>
194DELETE <replaceable class="parameter">count</replaceable>
195</screen>
196   The <replaceable class="parameter">count</replaceable> is the number
197   of rows deleted.  Note that the number may be less than the number of
198   rows that matched the <replaceable
199   class="parameter">condition</replaceable> when deletes were
200   suppressed by a <literal>BEFORE DELETE</literal> trigger.  If <replaceable
201   class="parameter">count</replaceable> is 0, no rows were deleted by
202   the query (this is not considered an error).
203  </para>
204
205  <para>
206   If the <command>DELETE</command> command contains a <literal>RETURNING</literal>
207   clause, the result will be similar to that of a <command>SELECT</command>
208   statement containing the columns and values defined in the
209   <literal>RETURNING</literal> list, computed over the row(s) deleted by the
210   command.
211  </para>
212 </refsect1>
213
214 <refsect1>
215  <title>Notes</title>
216
217  <para>
218   <productname>PostgreSQL</productname> lets you reference columns of
219   other tables in the <literal>WHERE</literal> condition by specifying the
220   other tables in the <literal>USING</literal> clause.  For example,
221   to delete all films produced by a given producer, one can do:
222<programlisting>
223DELETE FROM films USING producers
224  WHERE producer_id = producers.id AND producers.name = 'foo';
225</programlisting>
226   What is essentially happening here is a join between <structname>films</structname>
227   and <structname>producers</structname>, with all successfully joined
228   <structname>films</structname> rows being marked for deletion.
229   This syntax is not standard.  A more standard way to do it is:
230<programlisting>
231DELETE FROM films
232  WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');
233</programlisting>
234   In some cases the join style is easier to write or faster to
235   execute than the sub-select style.
236  </para>
237 </refsect1>
238
239 <refsect1>
240  <title>Examples</title>
241
242  <para>
243   Delete all films but musicals:
244<programlisting>
245DELETE FROM films WHERE kind &lt;&gt; 'Musical';
246</programlisting>
247  </para>
248
249  <para>
250   Clear the table <literal>films</literal>:
251<programlisting>
252DELETE FROM films;
253</programlisting>
254  </para>
255
256  <para>
257   Delete completed tasks, returning full details of the deleted rows:
258<programlisting>
259DELETE FROM tasks WHERE status = 'DONE' RETURNING *;
260</programlisting>
261  </para>
262
263   <para>
264   Delete the row of <structname>tasks</structname> on which the cursor
265   <literal>c_tasks</literal> is currently positioned:
266<programlisting>
267DELETE FROM tasks WHERE CURRENT OF c_tasks;
268</programlisting></para>
269 </refsect1>
270
271 <refsect1>
272  <title>Compatibility</title>
273
274  <para>
275   This command conforms to the <acronym>SQL</acronym> standard, except
276   that the <literal>USING</literal> and <literal>RETURNING</literal> clauses
277   are <productname>PostgreSQL</productname> extensions, as is the ability
278   to use <literal>WITH</literal> with <command>DELETE</command>.
279  </para>
280 </refsect1>
281
282 <refsect1>
283  <title>See Also</title>
284
285  <simplelist type="inline">
286   <member><xref linkend="sql-truncate"/></member>
287  </simplelist>
288 </refsect1>
289</refentry>
290