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