Debian Codegraph @

doc/src/sgml/ref/truncate.sgml

raw
1 <!--
2 doc/src/sgml/ref/truncate.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-truncate">
7 <indexterm zone="sql-truncate">
8 <primary>TRUNCATE</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>TRUNCATE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>TRUNCATE</refname>
19 <refpurpose>empty a table or set of tables</refpurpose>
20 </refnamediv>
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 truncate.
51 If <literal>ONLY</literal> is specified before the table name, only that table
52 is truncated. If <literal>ONLY</literal> is not specified, the table and all
53 its descendant tables (if any) are truncated. Optionally, <literal>*</literal>
54 can be specified after the table name to explicitly indicate that
55 descendant tables are included.
56 </para>
57 </listitem>
58 </varlistentry>
59
60 <varlistentry>
61 <term><literal>RESTART IDENTITY</literal></term>
62 <listitem>
63 <para>
64 Automatically restart sequences owned by columns of
65 the truncated table(s).
66 </para>
67 </listitem>
68 </varlistentry>
69
70 <varlistentry>
71 <term><literal>CONTINUE IDENTITY</literal></term>
72 <listitem>
73 <para>
74 Do not change the values of sequences. This is the default.
75 </para>
76 </listitem>
77 </varlistentry>
78
79 <varlistentry>
80 <term><literal>CASCADE</literal></term>
81 <listitem>
82 <para>
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>.
86 </para>
87 </listitem>
88 </varlistentry>
89
90 <varlistentry>
91 <term><literal>RESTRICT</literal></term>
92 <listitem>
93 <para>
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.
96 </para>
97 </listitem>
98 </varlistentry>
99 </variablelist>
100 </refsect1>
101
102 <refsect1>
103 <title>Notes</title>
104
105 <para>
106 You must have the <literal>TRUNCATE</literal> privilege on a table
107 to truncate it.
108 </para>
109
110 <para>
111 <command>TRUNCATE</command> acquires an <literal>ACCESS EXCLUSIVE</literal> lock on each
112 table it operates on, which blocks all other concurrent operations
113 on the table. When <literal>RESTART IDENTITY</literal> 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> command should be used instead.
117 </para>
118
119 <para>
120 <command>TRUNCATE</command> 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</literal>
124 option can be used to automatically include all dependent tables &mdash;
125 but be very careful when using this option, or else you might lose data you
126 did not intend to!
127 Note in particular that when the table to be truncated is a partition,
128 siblings partitions are left untouched, but cascading occurs to all
129 referencing tables and all their partitions with no distinction.
130 </para>
131
132 <para>
133 <command>TRUNCATE</command> will not fire any <literal>ON DELETE</literal>
134 triggers that might exist for the tables. But it will fire
135 <literal>ON TRUNCATE</literal> triggers.
136 If <literal>ON TRUNCATE</literal> triggers are defined for any of
137 the tables, then all <literal>BEFORE TRUNCATE</literal> triggers are
138 fired before any truncation happens, and all <literal>AFTER
139 TRUNCATE</literal> triggers are fired after the last truncation is
140 performed and any sequences are reset.
141 The triggers will fire in the order that the tables are
142 to be processed (first those listed in the command, and then any
143 that were added due to cascading).
144 </para>
145
146 <para>
147 <command>TRUNCATE</command> is not MVCC-safe. After truncation, the table will
148 appear empty to concurrent transactions, if they are using a snapshot
149 taken before the truncation occurred.
150 See <xref linkend="mvcc-caveats"/> for more details.
151 </para>
152
153 <para>
154 <command>TRUNCATE</command> is transaction-safe with respect to the data
155 in the tables: the truncation will be safely rolled back if the surrounding
156 transaction does not commit.
157 </para>
158
159 <para>
160 When <literal>RESTART IDENTITY</literal> is specified, the implied
161 <command>ALTER SEQUENCE RESTART</command> operations are also done
162 transactionally; that is, they will be rolled back if the surrounding
163 transaction does not commit. Be aware that if any additional
164 sequence operations are done on the restarted sequences before the
165 transaction rolls back, the effects of these operations on the sequences
166 will be rolled back, but not their effects on <function>currval()</function>;
167 that is, after the transaction <function>currval()</function> will continue to
168 reflect the last sequence value obtained inside the failed transaction,
169 even though the sequence itself may no longer be consistent with that.
170 This is similar to the usual behavior of <function>currval()</function> after
171 a failed transaction.
172 </para>
173
174 <para>
175 <command>TRUNCATE</command> can be used for foreign tables if
176 supported by the foreign data wrapper, for instance,
177 see <xref linkend="postgres-fdw"/>.
178 </para>
179 </refsect1>
180
181 <refsect1>
182 <title>Examples</title>
183
184 <para>
185 Truncate the tables <literal>bigtable</literal> and
186 <literal>fattable</literal>:
187
188 <programlisting>
189 TRUNCATE bigtable, fattable;
190 </programlisting>
191 </para>
192
193 <para>
194 The same, and also reset any associated sequence generators:
195
196 <programlisting>
197 TRUNCATE bigtable, fattable RESTART IDENTITY;
198 </programlisting>
199 </para>
200
201 <para>
202 Truncate the table <literal>othertable</literal>, and cascade to any tables
203 that reference <literal>othertable</literal> via foreign-key
204 constraints:
205
206 <programlisting>
207 TRUNCATE othertable CASCADE;
208 </programlisting></para>
209 </refsect1>
210
211 <refsect1>
212 <title>Compatibility</title>
213
214 <para>
215 The SQL:2008 standard includes a <command>TRUNCATE</command> command
216 with the syntax <literal>TRUNCATE TABLE
217 <replaceable>tablename</replaceable></literal>. The clauses
218 <literal>CONTINUE IDENTITY</literal>/<literal>RESTART IDENTITY</literal>
219 also appear in that standard, but have slightly different though related
220 meanings. Some of the concurrency behavior of this command is left
221 implementation-defined by the standard, so the above notes should be
222 considered and compared with other implementations if necessary.
223 </para>
224 </refsect1>
225
226 <refsect1>
227 <title>See Also</title>
228
229 <simplelist type="inline">
230 <member><xref linkend="sql-delete"/></member>
231 </simplelist>
232 </refsect1>
233 </refentry>