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 <> '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