2 doc/src/sgml/ref/truncate.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-TRUNCATE">
7 <indexterm zone="sql-truncate">
8 <primary>TRUNCATE</primary>
12 <refentrytitle>TRUNCATE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
18 <refname>TRUNCATE</refname>
19 <refpurpose>empty a table or set of tables</refpurpose>
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 truncate.
51 If <literal>ONLY</> is specified before the table name, only that table
52 is truncated. If <literal>ONLY</> is not specified, the table and all
53 its descendant tables (if any) are truncated. Optionally, <literal>*</>
54 can be specified after the table name to explicitly indicate that
55 descendant tables are included.
61 <term><literal>RESTART IDENTITY</literal></term>
64 Automatically restart sequences owned by columns of
65 the truncated table(s).
71 <term><literal>CONTINUE IDENTITY</literal></term>
74 Do not change the values of sequences. This is the default.
80 <term><literal>CASCADE</literal></term>
83 Automatically truncate all tables that have foreign-key references
84 to any of the named tables, or to any tables added to the group
85 due to <literal>CASCADE</literal>.
91 <term><literal>RESTRICT</literal></term>
94 Refuse to truncate if any of the tables have foreign-key references
95 from tables that are not listed in the command. This is the default.
106 You must have the <literal>TRUNCATE</literal> privilege on a table
111 <command>TRUNCATE</> acquires an <literal>ACCESS EXCLUSIVE</> lock on each
112 table it operates on, which blocks all other concurrent operations
113 on the table. When <literal>RESTART IDENTITY</> is specified, any
114 sequences that are to be restarted are likewise locked exclusively.
115 If concurrent access to a table is required, then
116 the <command>DELETE</> command should be used instead.
120 <command>TRUNCATE</> cannot be used on a table that has foreign-key
121 references from other tables, unless all such tables are also truncated
122 in the same command. Checking validity in such cases would require table
123 scans, and the whole point is not to do one. The <literal>CASCADE</>
124 option can be used to automatically include all dependent tables —
125 but be very careful when using this option, or else you might lose data you
130 <command>TRUNCATE</> will not fire any <literal>ON DELETE</literal>
131 triggers that might exist for the tables. But it will fire
132 <literal>ON TRUNCATE</literal> triggers.
133 If <literal>ON TRUNCATE</> triggers are defined for any of
134 the tables, then all <literal>BEFORE TRUNCATE</literal> triggers are
135 fired before any truncation happens, and all <literal>AFTER
136 TRUNCATE</literal> triggers are fired after the last truncation is
137 performed and any sequences are reset.
138 The triggers will fire in the order that the tables are
139 to be processed (first those listed in the command, and then any
140 that were added due to cascading).
144 <command>TRUNCATE</> is not MVCC-safe. After truncation, the table will
145 appear empty to concurrent transactions, if they are using a snapshot
146 taken before the truncation occurred.
147 See <xref linkend="mvcc-caveats"> for more details.
151 <command>TRUNCATE</> is transaction-safe with respect to the data
152 in the tables: the truncation will be safely rolled back if the surrounding
153 transaction does not commit.
157 When <literal>RESTART IDENTITY</> is specified, the implied
158 <command>ALTER SEQUENCE RESTART</> operations are also done
159 transactionally; that is, they will be rolled back if the surrounding
160 transaction does not commit. This is unlike the normal behavior of
161 <command>ALTER SEQUENCE RESTART</>. Be aware that if any additional
162 sequence operations are done on the restarted sequences before the
163 transaction rolls back, the effects of these operations on the sequences
164 will be rolled back, but not their effects on <function>currval()</>;
165 that is, after the transaction <function>currval()</> will continue to
166 reflect the last sequence value obtained inside the failed transaction,
167 even though the sequence itself may no longer be consistent with that.
168 This is similar to the usual behavior of <function>currval()</> after
169 a failed transaction.
173 <command>TRUNCATE</> is not currently supported for foreign tables.
174 This implies that if a specified table has any descendant tables that are
175 foreign, the command will fail.
180 <title>Examples</title>
183 Truncate the tables <literal>bigtable</literal> and
184 <literal>fattable</literal>:
187 TRUNCATE bigtable, fattable;
192 The same, and also reset any associated sequence generators:
195 TRUNCATE bigtable, fattable RESTART IDENTITY;
200 Truncate the table <literal>othertable</literal>, and cascade to any tables
201 that reference <literal>othertable</literal> via foreign-key
205 TRUNCATE othertable CASCADE;
206 </programlisting></para>
210 <title>Compatibility</title>
213 The SQL:2008 standard includes a <command>TRUNCATE</command> command
214 with the syntax <literal>TRUNCATE TABLE
215 <replaceable>tablename</replaceable></literal>. The clauses
216 <literal>CONTINUE IDENTITY</literal>/<literal>RESTART IDENTITY</literal>
217 also appear in that standard, but have slightly different though related
218 meanings. Some of the concurrency behavior of this command is left
219 implementation-defined by the standard, so the above notes should be
220 considered and compared with other implementations if necessary.
225 <title>See Also</title>
227 <simplelist type="inline">
228 <member><xref linkend="sql-delete"></member>