doc/src/sgml/ref/delete.sgml

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