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 truncate.
23 If ONLY is specified before the table name, only that table is
24 truncated. If ONLY is not specified, the table and all its
25 descendant tables (if any) are truncated. Optionally, * can be
26 specified after the table name to explicitly indicate that
27 descendant tables are included.
28 RESTART IDENTITY
30 Automatically restart sequences owned by columns of the trun‐
31 cated table(s).
32 CONTINUE IDENTITY
34 Do not change the values of sequences. This is the default.
35 CASCADE
37 Automatically truncate all tables that have foreign-key refer‐
38 ences to any of the named tables, or to any tables added to the
39 group due to CASCADE.
40 RESTRICT
42 Refuse to truncate if any of the tables have foreign-key refer‐
43 ences from tables that are not listed in the command. This is
44 the default.
45
47 You must have the TRUNCATE privilege on a table to truncate it.
48 TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates
50 on, which blocks all other concurrent operations on the table. If con‐
51 current access to a table is required, then the DELETE command should
52 be used instead.
53 TRUNCATE cannot be used on a table that has foreign-key references from
55 other tables, unless all such tables are also truncated in the same
56 command. Checking validity in such cases would require table scans, and
57 the whole point is not to do one. The CASCADE option can be used to
58 automatically include all dependent tables — but be very careful when
59 using this option, or else you might lose data you did not intend to!
60 TRUNCATE will not fire any ON DELETE triggers that might exist for the
62 tables. But it will fire ON TRUNCATE triggers. If ON TRUNCATE triggers
63 are defined for any of the tables, then all BEFORE TRUNCATE triggers
64 are fired before any truncation happens, and all AFTER TRUNCATE trig‐
65 gers are fired after the last truncation is performed. The triggers
66 will fire in the order that the tables are to be processed (first those
67 listed in the command, and then any that were added due to cascading).
68 Warning: TRUNCATE is not MVCC-safe (see in the documentation for
70 general information about MVCC). After truncation, the table
71 will appear empty to all concurrent transactions, even if they
72 are using a snapshot taken before the truncation occurred. This
73 will only be an issue for a transaction that did not access the
74 truncated table before the truncation happened — any transaction
75 that has done so would hold at least an ACCESS SHARE lock, which
76 would block TRUNCATE until that transaction completes. So trun‐
77 cation will not cause any apparent inconsistency in the table
78 contents for successive queries on the same table, but it could
79 cause visible inconsistency between the contents of the trun‐
80 cated table and other tables in the database.
81 TRUNCATE is transaction-safe with respect to the data in the tables:
84 the truncation will be safely rolled back if the surrounding transac‐
85 tion does not commit.
86 Warning: Any ALTER SEQUENCE RESTART operations performed as a
88 consequence of using the RESTART IDENTITY option are nontransac‐
89 tional and will not be rolled back on failure. To minimize the
90 risk, these operations are performed only after all the rest of
91 TRUNCATE's work is done. However, there is still a risk if TRUN‐
92 CATE is performed inside a transaction block that is aborted
93 afterwards. For example, consider
94 BEGIN;
96 TRUNCATE TABLE foo RESTART IDENTITY;
97 COPY foo FROM ...;
98 COMMIT;
99 If the COPY fails partway through, the table data rolls back
101 correctly, but the sequences will be left with values that are
102 probably smaller than they had before, possibly leading to
103 duplicate-key failures or other problems in later transactions.
104 If this is likely to be a problem, it's best to avoid using
105 RESTART IDENTITY, and accept that the new contents of the table
106 will have higher serial numbers than the old.
107
110 Truncate the tables bigtable and fattable:
111 TRUNCATE bigtable, fattable;
113 The same, and also reset any associated sequence generators:
116 TRUNCATE bigtable, fattable RESTART IDENTITY;
118 Truncate the table othertable, and cascade to any tables that reference
121 othertable via foreign-key constraints:
122 TRUNCATE othertable CASCADE;
124
127 The SQL:2008 standard includes a TRUNCATE command with the syntax TRUN‐
128 CATE TABLE tablename. The clauses CONTINUE IDENTITY/RESTART IDENTITY
129 also appear in that standard but have slightly different but related
130 meanings. Some of the concurrency behavior of this command is left
131 implementation-defined by the standard, so the above notes should be
132 considered and compared with other implementations if necessary.
133SQL - Language Statements 2014-02-17 TRUNCATE(7)