Debian Codegraph @

doc/src/sgml/ref/delete.sgml

raw
1 <!--
2 doc/src/sgml/ref/delete.sgml
3 PostgreSQL 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> [, ...] ]
25 DELETE 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 [ WITH ( { OLD | NEW } AS <replaceable class="parameter">output_alias</replaceable> [, ...] ) ]
29 { * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ]
30 </synopsis>
31 </refsynopsisdiv>
32
33 <refsect1>
34 <title>Description</title>
35
36 <para>
37 <command>DELETE</command> deletes rows that satisfy the
38 <literal>WHERE</literal> clause from the specified table. If the
39 <literal>WHERE</literal> clause is absent, the effect is to delete
40 all rows in the table. The result is a valid, but empty table.
41 </para>
42
43 <tip>
44 <para>
45 <link linkend="sql-truncate"><command>TRUNCATE</command></link> provides a
46 faster mechanism to remove all rows from a table.
47 </para>
48 </tip>
49
50 <para>
51 There are two ways to delete rows in a table using information
52 contained in other tables in the database: using sub-selects, or
53 specifying additional tables in the <literal>USING</literal> clause.
54 Which technique is more appropriate depends on the specific
55 circumstances.
56 </para>
57
58 <para>
59 The optional <literal>RETURNING</literal> clause causes <command>DELETE</command>
60 to compute and return value(s) based on each row actually deleted.
61 Any expression using the table's columns, and/or columns of other
62 tables mentioned in <literal>USING</literal>, can be computed.
63 The syntax of the <literal>RETURNING</literal> list is identical to that of the
64 output list of <command>SELECT</command>.
65 </para>
66
67 <para>
68 You must have the <literal>DELETE</literal> privilege on the table
69 to delete from it, as well as the <literal>SELECT</literal>
70 privilege for any table in the <literal>USING</literal> clause or
71 whose values are read in the <replaceable
72 class="parameter">condition</replaceable>.
73 </para>
74 </refsect1>
75
76 <refsect1>
77 <title>Parameters</title>
78
79 <variablelist>
80 <varlistentry>
81 <term><replaceable class="parameter">with_query</replaceable></term>
82 <listitem>
83 <para>
84 The <literal>WITH</literal> clause allows you to specify one or more
85 subqueries that can be referenced by name in the <command>DELETE</command>
86 query. See <xref linkend="queries-with"/> and <xref linkend="sql-select"/>
87 for details.
88 </para>
89 </listitem>
90 </varlistentry>
91
92 <varlistentry>
93 <term><replaceable class="parameter">table_name</replaceable></term>
94 <listitem>
95 <para>
96 The name (optionally schema-qualified) of the table to delete rows
97 from. If <literal>ONLY</literal> is specified before the table name,
98 matching rows are deleted from the named table only. If
99 <literal>ONLY</literal> is not specified, matching rows are also deleted
100 from any tables inheriting from the named table. Optionally,
101 <literal>*</literal> can be specified after the table name to explicitly
102 indicate that descendant tables are included.
103 </para>
104 </listitem>
105 </varlistentry>
106
107 <varlistentry>
108 <term><replaceable class="parameter">alias</replaceable></term>
109 <listitem>
110 <para>
111 A substitute name for the target table. When an alias is
112 provided, it completely hides the actual name of the table. For
113 example, given <literal>DELETE FROM foo AS f</literal>, the remainder
114 of the <command>DELETE</command> statement must refer to this
115 table as <literal>f</literal> not <literal>foo</literal>.
116 </para>
117 </listitem>
118 </varlistentry>
119
120 <varlistentry>
121 <term><replaceable class="parameter">from_item</replaceable></term>
122 <listitem>
123 <para>
124 A table expression allowing columns from other tables to appear
125 in the <literal>WHERE</literal> condition. This uses the same
126 syntax as the <link linkend="sql-from"><literal>FROM</literal></link>
127 clause of a <command>SELECT</command> statement; for example, an alias
128 for the table name can be specified. Do not repeat the target
129 table as a <replaceable class="parameter">from_item</replaceable>
130 unless you wish to set up a self-join (in which case it must appear
131 with an alias in the <replaceable>from_item</replaceable>).
132 </para>
133 </listitem>
134 </varlistentry>
135
136 <varlistentry>
137 <term><replaceable class="parameter">condition</replaceable></term>
138 <listitem>
139 <para>
140 An expression that returns a value of type <type>boolean</type>.
141 Only rows for which this expression returns <literal>true</literal>
142 will be deleted.
143 </para>
144 </listitem>
145 </varlistentry>
146
147 <varlistentry>
148 <term><replaceable class="parameter">cursor_name</replaceable></term>
149 <listitem>
150 <para>
151 The name of the cursor to use in a <literal>WHERE CURRENT OF</literal>
152 condition. The row to be deleted is the one most recently fetched
153 from this cursor. The cursor must be a non-grouping
154 query on the <command>DELETE</command>'s target table.
155 Note that <literal>WHERE CURRENT OF</literal> cannot be
156 specified together with a Boolean condition. See
157 <xref linkend="sql-declare"/>
158 for more information about using cursors with
159 <literal>WHERE CURRENT OF</literal>.
160 </para>
161 </listitem>
162 </varlistentry>
163
164 <varlistentry>
165 <term><replaceable class="parameter">output_alias</replaceable></term>
166 <listitem>
167 <para>
168 An optional substitute name for <literal>OLD</literal> or
169 <literal>NEW</literal> rows in the <literal>RETURNING</literal> list.
170 </para>
171
172 <para>
173 By default, old values from the target table can be returned by writing
174 <literal>OLD.<replaceable class="parameter">column_name</replaceable></literal>
175 or <literal>OLD.*</literal>, and new values can be returned by writing
176 <literal>NEW.<replaceable class="parameter">column_name</replaceable></literal>
177 or <literal>NEW.*</literal>. When an alias is provided, these names are
178 hidden and the old or new rows must be referred to using the alias.
179 For example <literal>RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*</literal>.
180 </para>
181 </listitem>
182 </varlistentry>
183
184 <varlistentry>
185 <term><replaceable class="parameter">output_expression</replaceable></term>
186 <listitem>
187 <para>
188 An expression to be computed and returned by the <command>DELETE</command>
189 command after each row is deleted. The expression can use any
190 column names of the table named by <replaceable class="parameter">table_name</replaceable>
191 or table(s) listed in <literal>USING</literal>.
192 Write <literal>*</literal> to return all columns.
193 </para>
194
195 <para>
196 A column name or <literal>*</literal> may be qualified using
197 <literal>OLD</literal> or <literal>NEW</literal>, or the corresponding
198 <replaceable class="parameter">output_alias</replaceable> for
199 <literal>OLD</literal> or <literal>NEW</literal>, to cause old or new
200 values to be returned. An unqualified column name, or
201 <literal>*</literal>, or a column name or <literal>*</literal> qualified
202 using the target table name or alias will return old values.
203 </para>
204
205 <para>
206 For a simple <command>DELETE</command>, all new values will be
207 <literal>NULL</literal>. However, if an <literal>ON DELETE</literal>
208 rule causes an <command>INSERT</command> or <command>UPDATE</command>
209 to be executed instead, the new values may be non-<literal>NULL</literal>.
210 </para>
211 </listitem>
212 </varlistentry>
213
214 <varlistentry>
215 <term><replaceable class="parameter">output_name</replaceable></term>
216 <listitem>
217 <para>
218 A name to use for a returned column.
219 </para>
220 </listitem>
221 </varlistentry>
222 </variablelist>
223 </refsect1>
224
225 <refsect1>
226 <title>Outputs</title>
227
228 <para>
229 On successful completion, a <command>DELETE</command> command returns a command
230 tag of the form
231 <screen>
232 DELETE <replaceable class="parameter">count</replaceable>
233 </screen>
234 The <replaceable class="parameter">count</replaceable> is the number
235 of rows deleted. Note that the number may be less than the number of
236 rows that matched the <replaceable
237 class="parameter">condition</replaceable> when deletes were
238 suppressed by a <literal>BEFORE DELETE</literal> trigger. If <replaceable
239 class="parameter">count</replaceable> is 0, no rows were deleted by
240 the query (this is not considered an error).
241 </para>
242
243 <para>
244 If the <command>DELETE</command> command contains a <literal>RETURNING</literal>
245 clause, the result will be similar to that of a <command>SELECT</command>
246 statement containing the columns and values defined in the
247 <literal>RETURNING</literal> list, computed over the row(s) deleted by the
248 command.
249 </para>
250 </refsect1>
251
252 <refsect1>
253 <title>Notes</title>
254
255 <para>
256 <productname>PostgreSQL</productname> lets you reference columns of
257 other tables in the <literal>WHERE</literal> condition by specifying the
258 other tables in the <literal>USING</literal> clause. For example,
259 to delete all films produced by a given producer, one can do:
260 <programlisting>
261 DELETE FROM films USING producers
262 WHERE producer_id = producers.id AND producers.name = 'foo';
263 </programlisting>
264 What is essentially happening here is a join between <structname>films</structname>
265 and <structname>producers</structname>, with all successfully joined
266 <structname>films</structname> rows being marked for deletion.
267 This syntax is not standard. A more standard way to do it is:
268 <programlisting>
269 DELETE FROM films
270 WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');
271 </programlisting>
272 In some cases the join style is easier to write or faster to
273 execute than the sub-select style.
274 </para>
275 </refsect1>
276
277 <refsect1>
278 <title>Examples</title>
279
280 <para>
281 Delete all films but musicals:
282 <programlisting>
283 DELETE FROM films WHERE kind &lt;&gt; 'Musical';
284 </programlisting>
285 </para>
286
287 <para>
288 Clear the table <literal>films</literal>:
289 <programlisting>
290 DELETE FROM films;
291 </programlisting>
292 </para>
293
294 <para>
295 Delete completed tasks, returning full details of the deleted rows:
296 <programlisting>
297 DELETE FROM tasks WHERE status = 'DONE' RETURNING *;
298 </programlisting>
299 </para>
300
301 <para>
302 Delete the row of <structname>tasks</structname> on which the cursor
303 <literal>c_tasks</literal> is currently positioned:
304 <programlisting>
305 DELETE FROM tasks WHERE CURRENT OF c_tasks;
306 </programlisting>
307 </para>
308
309 <para>
310 While there is no <literal>LIMIT</literal> clause
311 for <command>DELETE</command>, it is possible to get a similar effect
312 using the same method described in <link linkend="update-limit">the
313 documentation of <command>UPDATE</command></link>:
314 <programlisting>
315 WITH delete_batch AS (
316 SELECT l.ctid FROM user_logs AS l
317 WHERE l.status = 'archived'
318 ORDER BY l.creation_date
319 FOR UPDATE
320 LIMIT 10000
321 )
322 DELETE FROM user_logs AS dl
323 USING delete_batch AS del
324 WHERE dl.ctid = del.ctid;
325 </programlisting>
326 This use of <structfield>ctid</structfield> is only safe because
327 the query is repeatedly run, avoiding the problem of changed
328 <structfield>ctid</structfield>s.
329 </para>
330 </refsect1>
331
332 <refsect1>
333 <title>Compatibility</title>
334
335 <para>
336 This command conforms to the <acronym>SQL</acronym> standard, except
337 that the <literal>USING</literal> and <literal>RETURNING</literal> clauses
338 are <productname>PostgreSQL</productname> extensions, as is the ability
339 to use <literal>WITH</literal> with <command>DELETE</command>.
340 </para>
341 </refsect1>
342
343 <refsect1>
344 <title>See Also</title>
345
346 <simplelist type="inline">
347 <member><xref linkend="sql-truncate"/></member>
348 </simplelist>
349 </refsect1>
350 </refentry>