2 doc/src/sgml/ref/truncate.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-TRUNCATE">
8 <refentrytitle>TRUNCATE</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>TRUNCATE</refname>
15 <refpurpose>empty a table or set of tables</refpurpose>
18 <indexterm zone="sql-truncate">
19 <primary>TRUNCATE</primary>
24 TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [, ... ]
25 [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
30 <title>Description</title>
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.
43 <title>Parameters</title>
47 <term><replaceable class="PARAMETER">name</replaceable></term>
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.
59 <term><literal>RESTART IDENTITY</literal></term>
62 Automatically restart sequences owned by columns of
63 the truncated table(s).
69 <term><literal>CONTINUE IDENTITY</literal></term>
72 Do not change the values of sequences. This is the default.
78 <term><literal>CASCADE</literal></term>
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>.
89 <term><literal>RESTRICT</literal></term>
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.
104 You must have the <literal>TRUNCATE</literal> privilege on a table
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. When <literal>RESTART IDENTITY</> is specified, any
112 sequences that are to be restarted are likewise locked exclusively.
113 If concurrent access to a table is required, then
114 the <command>DELETE</> command should be used instead.
118 <command>TRUNCATE</> cannot be used on a table that has foreign-key
119 references from other tables, unless all such tables are also truncated
120 in the same command. Checking validity in such cases would require table
121 scans, and the whole point is not to do one. The <literal>CASCADE</>
122 option can be used to automatically include all dependent tables —
123 but be very careful when using this option, or else you might lose data you
128 <command>TRUNCATE</> will not fire any <literal>ON DELETE</literal>
129 triggers that might exist for the tables. But it will fire
130 <literal>ON TRUNCATE</literal> triggers.
131 If <literal>ON TRUNCATE</> triggers are defined for any of
132 the tables, then all <literal>BEFORE TRUNCATE</literal> triggers are
133 fired before any truncation happens, and all <literal>AFTER
134 TRUNCATE</literal> triggers are fired after the last truncation is
135 performed and any sequences are reset.
136 The triggers will fire in the order that the tables are
137 to be processed (first those listed in the command, and then any
138 that were added due to cascading).
143 <command>TRUNCATE</> is not MVCC-safe (see <xref linkend="mvcc">
144 for general information about MVCC). After truncation, the table
145 will appear empty to all concurrent transactions, even if they
146 are using a snapshot taken before the truncation occurred. This
147 will only be an issue for a transaction that did not access the
148 truncated table before the truncation happened — any
149 transaction that has done so would hold at least an
150 <literal>ACCESS SHARE</literal> lock, which would block
151 <command>TRUNCATE</> until that transaction completes. So
152 truncation will not cause any apparent inconsistency in the table
153 contents for successive queries on the same table, but it could
154 cause visible inconsistency between the contents of the truncated
155 table and other tables in the database.
160 <command>TRUNCATE</> is transaction-safe with respect to the data
161 in the tables: the truncation will be safely rolled back if the surrounding
162 transaction does not commit.
166 When <literal>RESTART IDENTITY</> is specified, the implied
167 <command>ALTER SEQUENCE RESTART</> operations are also done
168 transactionally; that is, they will be rolled back if the surrounding
169 transaction does not commit. This is unlike the normal behavior of
170 <command>ALTER SEQUENCE RESTART</>. Be aware that if any additional
171 sequence operations are done on the restarted sequences before the
172 transaction rolls back, the effects of these operations on the sequences
173 will be rolled back, but not their effects on <function>currval()</>;
174 that is, after the transaction <function>currval()</> will continue to
175 reflect the last sequence value obtained inside the failed transaction,
176 even though the sequence itself may no longer be consistent with that.
177 This is similar to the usual behavior of <function>currval()</> after
178 a failed transaction.
183 <title>Examples</title>
186 Truncate the tables <literal>bigtable</literal> and
187 <literal>fattable</literal>:
190 TRUNCATE bigtable, fattable;
195 The same, and also reset any associated sequence generators:
198 TRUNCATE bigtable, fattable RESTART IDENTITY;
203 Truncate the table <literal>othertable</literal>, and cascade to any tables
204 that reference <literal>othertable</literal> via foreign-key
208 TRUNCATE othertable CASCADE;
209 </programlisting></para>
213 <title>Compatibility</title>
216 The SQL:2008 standard includes a <command>TRUNCATE</command> command
217 with the syntax <literal>TRUNCATE TABLE
218 <replaceable>tablename</replaceable></literal>. The clauses
219 <literal>CONTINUE IDENTITY</literal>/<literal>RESTART IDENTITY</literal>
220 also appear in that standard, but have slightly different though related
221 meanings. Some of the concurrency behavior of this command is left
222 implementation-defined by the standard, so the above notes should be
223 considered and compared with other implementations if necessary.