doc/src/sgml/ref/truncate.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-TRUNCATE">
   7  <refmeta>
   8   <refentrytitle id="SQL-TRUNCATE-TITLE">TRUNCATE</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>TRUNCATE</refname>
  15   <refpurpose>empty a table or set of tables</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-truncate">
  19   <primary>TRUNCATE</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [, ... ]
  25     [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
  26 </synopsis>
  27  </refsynopsisdiv>
  28
  29  <refsect1>
  30   <title>Description</title>
  31
  32   <para>
  33    <command>TRUNCATE</command> quickly removes all rows from a set of
  34    tables. It has the same effect as an unqualified
  35    <command>DELETE</command> on each table, but since it does not actually
  36    scan the tables it is faster. Furthermore, it reclaims disk space
  37    immediately, rather than requiring a subsequent <command>VACUUM</command>
  38    operation. This is most useful on large tables.
  39   </para>
  40  </refsect1>
  41
  42  <refsect1>
  43   <title>Parameters</title>
  44
  45   <variablelist>
  46    <varlistentry>
  47     <term><replaceable class="PARAMETER">name</replaceable></term>
  48     <listitem>
  49      <para>
  50       The name (optionally schema-qualified) of a table to be
  51       truncated.  If <literal>ONLY</> is specified, only that table is
  52       truncated.  If <literal>ONLY</> is not specified, the table and
  53       all its descendant tables (if any) are truncated.
  54      </para>
  55     </listitem>
  56    </varlistentry>
  57
  58    <varlistentry>
  59     <term><literal>RESTART IDENTITY</literal></term>
  60     <listitem>
  61      <para>
  62       Automatically restart sequences owned by columns of
  63       the truncated table(s).
  64      </para>
  65     </listitem>
  66    </varlistentry>
  67
  68    <varlistentry>
  69     <term><literal>CONTINUE IDENTITY</literal></term>
  70     <listitem>
  71      <para>
  72       Do not change the values of sequences.  This is the default.
  73      </para>
  74     </listitem>
  75    </varlistentry>
  76
  77    <varlistentry>
  78     <term><literal>CASCADE</literal></term>
  79     <listitem>
  80      <para>
  81       Automatically truncate all tables that have foreign-key references
  82       to any of the named tables, or to any tables added to the group
  83       due to <literal>CASCADE</literal>.
  84      </para>
  85     </listitem>
  86    </varlistentry>
  87
  88    <varlistentry>
  89     <term><literal>RESTRICT</literal></term>
  90     <listitem>
  91      <para>
  92       Refuse to truncate if any of the tables have foreign-key references
  93       from tables that are not listed in the command.  This is the default.
  94      </para>
  95     </listitem>
  96    </varlistentry>
  97   </variablelist>
  98  </refsect1>
  99
 100  <refsect1>
 101   <title>Notes</title>
 102
 103   <para>
 104    You must have the <literal>TRUNCATE</literal> privilege on a table
 105    to truncate it.
 106   </para>
 107
 108   <para>
 109    <command>TRUNCATE</> acquires an <literal>ACCESS EXCLUSIVE</> lock on each
 110    table it operates on, which blocks all other concurrent operations
 111    on the table.  If concurrent access to a table is required, then
 112    the <command>DELETE</> command should be used instead.
 113   </para>
 114
 115   <para>
 116    <command>TRUNCATE</> cannot be used on a table that has foreign-key
 117    references from other tables, unless all such tables are also truncated
 118    in the same command.  Checking validity in such cases would require table
 119    scans, and the whole point is not to do one.  The <literal>CASCADE</>
 120    option can be used to automatically include all dependent tables &mdash;
 121    but be very careful when using this option, or else you might lose data you
 122    did not intend to!
 123   </para>
 124
 125   <para>
 126    <command>TRUNCATE</> will not fire any <literal>ON DELETE</literal>
 127    triggers that might exist for the tables.  But it will fire
 128    <literal>ON TRUNCATE</literal> triggers.
 129    If <literal>ON TRUNCATE</> triggers are defined for any of
 130    the tables, then all <literal>BEFORE TRUNCATE</literal> triggers are
 131    fired before any truncation happens, and all <literal>AFTER
 132    TRUNCATE</literal> triggers are fired after the last truncation is
 133    performed.  The triggers will fire in the order that the tables are
 134    to be processed (first those listed in the command, and then any
 135    that were added due to cascading).
 136   </para>
 137
 138   <warning>
 139    <para>
 140     <command>TRUNCATE</> is not MVCC-safe (see <xref linkend="mvcc">
 141      for general information about MVCC).  After truncation, the table
 142      will appear empty to all concurrent transactions, even if they
 143      are using a snapshot taken before the truncation occurred.  This
 144      will only be an issue for a transaction that did not access the
 145      truncated table before the truncation happened &mdash; any
 146      transaction that has done so would hold at least an
 147      <literal>ACCESS SHARE</literal> lock, which would block
 148      <command>TRUNCATE</> until that transaction completes.  So
 149      truncation will not cause any apparent inconsistency in the table
 150      contents for successive queries on the same table, but it could
 151      cause visible inconsistency between the contents of the truncated
 152      table and other tables in the database.
 153    </para>
 154   </warning>
 155
 156   <para>
 157    <command>TRUNCATE</> is transaction-safe with respect to the data
 158    in the tables: the truncation will be safely rolled back if the surrounding
 159    transaction does not commit.
 160   </para>
 161
 162   <warning>
 163    <para>
 164     Any <command>ALTER SEQUENCE RESTART</> operations performed as a
 165     consequence of using the <literal>RESTART IDENTITY</> option are
 166     nontransactional and will not be rolled back on failure.  To minimize
 167     the risk, these operations are performed only after all the rest of
 168     <command>TRUNCATE</>'s work is done.  However, there is still a risk
 169     if <command>TRUNCATE</> is performed inside a transaction block that is
 170     aborted afterwards.  For example, consider
 171
 172 <programlisting>
 173 BEGIN;
 174 TRUNCATE TABLE foo RESTART IDENTITY;
 175 COPY foo FROM ...;
 176 COMMIT;
 177 </programlisting>
 178
 179     If the <command>COPY</> fails partway through, the table data
 180     rolls back correctly, but the sequences will be left with values
 181     that are probably smaller than they had before, possibly leading
 182     to duplicate-key failures or other problems in later transactions.
 183     If this is likely to be a problem, it's best to avoid using
 184     <literal>RESTART IDENTITY</>, and accept that the new contents of
 185     the table will have higher serial numbers than the old.
 186    </para>
 187   </warning>
 188  </refsect1>
 189
 190  <refsect1>
 191   <title>Examples</title>
 192
 193   <para>
 194    Truncate the tables <literal>bigtable</literal> and
 195    <literal>fattable</literal>:
 196
 197 <programlisting>
 198 TRUNCATE bigtable, fattable;
 199 </programlisting>
 200   </para>
 201
 202   <para>
 203    The same, and also reset any associated sequence generators:
 204
 205 <programlisting>
 206 TRUNCATE bigtable, fattable RESTART IDENTITY;
 207 </programlisting>
 208   </para>
 209
 210   <para>
 211    Truncate the table <literal>othertable</literal>, and cascade to any tables
 212    that reference <literal>othertable</literal> via foreign-key
 213    constraints:
 214
 215 <programlisting>
 216 TRUNCATE othertable CASCADE;
 217 </programlisting>
 218   </para>
 219  </refsect1>
 220
 221  <refsect1>
 222   <title>Compatibility</title>
 223
 224   <para>
 225    The SQL:2008 standard includes a <command>TRUNCATE</command> command with the syntax
 226    <literal>TRUNCATE TABLE <replaceable>tablename</replaceable></literal>.
 227    The clauses <literal>CONTINUE IDENTITY</literal>/<literal>RESTART IDENTITY</literal>
 228    also appear in that standard but have slightly different but related meanings.
 229    Some of the concurrency behavior of this command is left implementation-defined
 230    by the standard, so the above notes should be considered and compared with
 231    other implementations if necessary.
 232   </para>
 233  </refsect1>
 234 </refentry>