2 doc/src/sgml/ref/delete.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-DELETE">
8 <refentrytitle>DELETE</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>DELETE</refname>
15 <refpurpose>delete rows of a table</refpurpose>
18 <indexterm zone="sql-delete">
19 <primary>DELETE</primary>
24 [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
25 DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
26 [ USING <replaceable class="PARAMETER">using_list</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> ] [, ...] ]
33 <title>Description</title>
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.
44 <xref linkend="sql-truncate"> is a
45 <productname>PostgreSQL</productname> extension that provides a
46 faster mechanism to remove all rows from a table.
51 By default, <command>DELETE</command> will delete rows in the
52 specified table and all its child tables. If you wish to delete only
53 from the specific table mentioned, you must use the
54 <literal>ONLY</literal> clause.
58 There are two ways to delete rows in a table using information
59 contained in other tables in the database: using sub-selects, or
60 specifying additional tables in the <literal>USING</literal> clause.
61 Which technique is more appropriate depends on the specific
66 The optional <literal>RETURNING</> clause causes <command>DELETE</>
67 to compute and return value(s) based on each row actually deleted.
68 Any expression using the table's columns, and/or columns of other
69 tables mentioned in <literal>USING</literal>, can be computed.
70 The syntax of the <literal>RETURNING</> list is identical to that of the
71 output list of <command>SELECT</>.
75 You must have the <literal>DELETE</literal> privilege on the table
76 to delete from it, as well as the <literal>SELECT</literal>
77 privilege for any table in the <literal>USING</literal> clause or
78 whose values are read in the <replaceable
79 class="parameter">condition</replaceable>.
84 <title>Parameters</title>
88 <term><replaceable class="parameter">with_query</replaceable></term>
91 The <literal>WITH</literal> clause allows you to specify one or more
92 subqueries that can be referenced by name in the <command>DELETE</>
93 query. See <xref linkend="queries-with"> and <xref linkend="sql-select">
100 <term><literal>ONLY</></term>
103 If specified, delete rows from the named table only. When not
104 specified, any tables inheriting from the named table are also processed.
110 <term><replaceable class="parameter">table</replaceable></term>
113 The name (optionally schema-qualified) of an existing table.
119 <term><replaceable class="parameter">alias</replaceable></term>
122 A substitute name for the target table. When an alias is
123 provided, it completely hides the actual name of the table. For
124 example, given <literal>DELETE FROM foo AS f</>, the remainder
125 of the <command>DELETE</command> statement must refer to this
126 table as <literal>f</> not <literal>foo</>.
132 <term><replaceable class="PARAMETER">using_list</replaceable></term>
135 A list of table expressions, allowing columns from other tables
136 to appear in the <literal>WHERE</> condition. This is similar
137 to the list of tables that can be specified in the <xref
138 linkend="sql-from" endterm="sql-from-title"> of a
139 <command>SELECT</command> statement; for example, an alias for
140 the table name can be specified. Do not repeat the target table
141 in the <replaceable class="PARAMETER">using_list</replaceable>,
142 unless you wish to set up a self-join.
148 <term><replaceable class="parameter">condition</replaceable></term>
151 An expression that returns a value of type <type>boolean</type>.
152 Only rows for which this expression returns <literal>true</>
159 <term><replaceable class="PARAMETER">cursor_name</replaceable></term>
162 The name of the cursor to use in a <literal>WHERE CURRENT OF</>
163 condition. The row to be deleted is the one most recently fetched
164 from this cursor. The cursor must be a non-grouping
165 query on the <command>DELETE</>'s target table.
166 Note that <literal>WHERE CURRENT OF</> cannot be
167 specified together with a Boolean condition. See
168 <xref linkend="sql-declare">
169 for more information about using cursors with
170 <literal>WHERE CURRENT OF</>.
176 <term><replaceable class="PARAMETER">output_expression</replaceable></term>
179 An expression to be computed and returned by the <command>DELETE</>
180 command after each row is deleted. The expression can use any
181 column names of the <replaceable class="PARAMETER">table</replaceable>
182 or table(s) listed in <literal>USING</>.
183 Write <literal>*</> to return all columns.
189 <term><replaceable class="PARAMETER">output_name</replaceable></term>
192 A name to use for a returned column.
200 <title>Outputs</title>
203 On successful completion, a <command>DELETE</> command returns a command
206 DELETE <replaceable class="parameter">count</replaceable>
208 The <replaceable class="parameter">count</replaceable> is the number
209 of rows deleted. Note that the number may be less than the number of
210 rows that matched the <replaceable
211 class="parameter">condition</replaceable> when deletes were
212 suppressed by a <literal>BEFORE DELETE</> trigger. If <replaceable
213 class="parameter">count</replaceable> is 0, no rows were deleted by
214 the query (this is not considered an error).
218 If the <command>DELETE</> command contains a <literal>RETURNING</>
219 clause, the result will be similar to that of a <command>SELECT</>
220 statement containing the columns and values defined in the
221 <literal>RETURNING</> list, computed over the row(s) deleted by the
230 <productname>PostgreSQL</productname> lets you reference columns of
231 other tables in the <literal>WHERE</> condition by specifying the
232 other tables in the <literal>USING</literal> clause. For example,
233 to delete all films produced by a given producer, one can do:
235 DELETE FROM films USING producers
236 WHERE producer_id = producers.id AND producers.name = 'foo';
238 What is essentially happening here is a join between <structname>films</>
239 and <structname>producers</>, with all successfully joined
240 <structname>films</> rows being marked for deletion.
241 This syntax is not standard. A more standard way to do it is:
244 WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');
246 In some cases the join style is easier to write or faster to
247 execute than the sub-select style.
252 <title>Examples</title>
255 Delete all films but musicals:
257 DELETE FROM films WHERE kind <> 'Musical';
262 Clear the table <literal>films</literal>:
269 Delete completed tasks, returning full details of the deleted rows:
271 DELETE FROM tasks WHERE status = 'DONE' RETURNING *;
276 Delete the row of <structname>tasks</> on which the cursor
277 <literal>c_tasks</> is currently positioned:
279 DELETE FROM tasks WHERE CURRENT OF c_tasks;
280 </programlisting></para>
284 <title>Compatibility</title>
287 This command conforms to the <acronym>SQL</acronym> standard, except
288 that the <literal>USING</literal> and <literal>RETURNING</> clauses
289 are <productname>PostgreSQL</productname> extensions, as is the ability
290 to use <literal>WITH</> with <command>DELETE</>.