1TRUNCATE(7) SQL Commands TRUNCATE(7)
2
6 TRUNCATE - empty a table or set of tables
7
10 TRUNCATE [ TABLE ] [ ONLY ] name [, ... ]
11 [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
12
15 TRUNCATE quickly removes all rows from a set of tables. It has the same
16 effect as an unqualified DELETE on each table, but since it does not
17 actually scan the tables it is faster. Furthermore, it reclaims disk
18 space immediately, rather than requiring a subsequent VACUUM operation.
19 This is most useful on large tables.
20
22 name The name (optionally schema-qualified) of a table to be trun‐
23 cated. If ONLY is specified, only that table is truncated. If
24 ONLY is not specified, the table and all its descendant tables
25 (if any) are truncated.
26 RESTART IDENTITY
28 Automatically restart sequences owned by columns of the trun‐
29 cated table(s).
30 CONTINUE IDENTITY
32 Do not change the values of sequences. This is the default.
33 CASCADE
35 Automatically truncate all tables that have foreign-key refer‐
36 ences to any of the named tables, or to any tables added to the
37 group due to CASCADE.
38 RESTRICT
40 Refuse to truncate if any of the tables have foreign-key refer‐
41 ences from tables that are not listed in the command. This is
42 the default.
43
45 You must have the TRUNCATE privilege on a table to truncate it.
46 TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates
48 on, which blocks all other concurrent operations on the table. If con‐
49 current access to a table is required, then the DELETE command should
50 be used instead.
51 TRUNCATE cannot be used on a table that has foreign-key references from
53 other tables, unless all such tables are also truncated in the same
54 command. Checking validity in such cases would require table scans, and
55 the whole point is not to do one. The CASCADE option can be used to
56 automatically include all dependent tables — but be very careful when
57 using this option, or else you might lose data you did not intend to!
58 TRUNCATE will not fire any ON DELETE triggers that might exist for the
60 tables. But it will fire ON TRUNCATE triggers. If ON TRUNCATE triggers
61 are defined for any of the tables, then all BEFORE TRUNCATE triggers
62 are fired before any truncation happens, and all AFTER TRUNCATE trig‐
63 gers are fired after the last truncation is performed. The triggers
64 will fire in the order that the tables are to be processed (first those
65 listed in the command, and then any that were added due to cascading).
66 Warning: TRUNCATE is not MVCC-safe (see in the documentation for
68 general information about MVCC). After truncation, the table
69 will appear empty to all concurrent transactions, even if they
70 are using a snapshot taken before the truncation occurred. This
71 will only be an issue for a transaction that did not access the
72 truncated table before the truncation happened — any transaction
73 that has done so would hold at least an ACCESS SHARE lock, which
74 would block TRUNCATE until that transaction completes. So trun‐
75 cation will not cause any apparent inconsistency in the table
76 contents for successive queries on the same table, but it could
77 cause visible inconsistency between the contents of the trun‐
78 cated table and other tables in the database.
79 TRUNCATE is transaction-safe with respect to the data in the tables:
82 the truncation will be safely rolled back if the surrounding transac‐
83 tion does not commit.
84 Warning: Any ALTER SEQUENCE RESTART operations performed as a
86 consequence of using the RESTART IDENTITY option are nontransac‐
87 tional and will not be rolled back on failure. To minimize the
88 risk, these operations are performed only after all the rest of
89 TRUNCATE's work is done. However, there is still a risk if TRUN‐
90 CATE is performed inside a transaction block that is aborted
91 afterwards. For example, consider
92 BEGIN;
94 TRUNCATE TABLE foo RESTART IDENTITY;
95 COPY foo FROM ...;
96 COMMIT;
97 If the COPY fails partway through, the table data rolls back
99 correctly, but the sequences will be left with values that are
100 probably smaller than they had before, possibly leading to
101 duplicate-key failures or other problems in later transactions.
102 If this is likely to be a problem, it's best to avoid using
103 RESTART IDENTITY, and accept that the new contents of the table
104 will have higher serial numbers than the old.
105
108 Truncate the tables bigtable and fattable:
109 TRUNCATE bigtable, fattable;
111 The same, and also reset any associated sequence generators:
114 TRUNCATE bigtable, fattable RESTART IDENTITY;
116 Truncate the table othertable, and cascade to any tables that reference
119 othertable via foreign-key constraints:
120 TRUNCATE othertable CASCADE;
122
125 The SQL:2008 standard includes a TRUNCATE command with the syntax TRUN‐
126 CATE TABLE tablename. The clauses CONTINUE IDENTITY/RESTART IDENTITY
127 also appear in that standard but have slightly different but related
128 meanings. Some of the concurrency behavior of this command is left
129 implementation-defined by the standard, so the above notes should be
130 considered and compared with other implementations if necessary.
131SQL - Language Statements 2011-09-22 TRUNCATE(7)