On this page 
Warning:
CockroachDB v1.0 is no longer supported. For more details, see the Release Support Policy.
The TRUNCATE statement deletes all rows from specified tables.
Note:
The TRUNCATE removes all rows from a table by dropping the table and recreating a new table with the same name. For large tables, this is much more performant than deleting each of the rows. However, for smaller tables, it's more performant to use a DELETE statement without a WHERE clause.Synopsis
Required Privileges
The user must have the DROP privilege on the table.
Parameters
| Parameter | Description |
|---|---|
table_name |
The qualified_name of the table to truncate. |
CASCADE |
Truncate all tables with Foreign Key dependencies on the table being truncated.CASCADE does not list dependent tables it truncates, so should be used cautiously. |
RESTRICT |
(Default) Do not truncate the table if any other tables have Foreign Key dependencies on it. |
Examples
Truncate a Table (No Foreign Key Dependencies)
> SELECT * FROM t1;
+----+------+
| id | name |
+----+------+
| 1 | foo |
| 2 | bar |
+----+------+
(2 rows)
> TRUNCATE t1;
> SELECT * FROM t1;
+----+------+
| id | name |
+----+------+
+----+------+
(0 rows)
Truncate a Table and Dependent Tables
In these examples, the orders table has a Foreign Key relationship to the customers table. Therefore, it's only possible to truncate the customers table while simultaneously truncating the dependent orders table, either using CASCADE or explicitly.
Truncate Dependent Tables Using CASCADE
Warning:
CASCADE truncates all dependent tables without listing them, which can lead to inadvertent and difficult-to-recover losses. To avoid potential harm, we recommend truncating tables explicitly in most cases. See Truncate Dependent Tables Explicitly for more details.> TRUNCATE customers;
pq: "customers" is referenced by foreign key from table "orders"
> TRUNCATE customers CASCADE;
> SELECT * FROM customers;
+----+-------+
| id | email |
+----+-------+
+----+-------+
(0 rows)
> SELECT * FROM orders;
+----+----------+------------+
| id | customer | orderTotal |
+----+----------+------------+
+----+----------+------------+
(0 rows)
Truncate Dependent Tables Explicitly
> TRUNCATE customers, orders;
> SELECT * FROM customers;
+----+-------+
| id | email |
+----+-------+
+----+-------+
(0 rows)
> SELECT * FROM orders;
+----+----------+------------+
| id | customer | orderTotal |
+----+----------+------------+
+----+----------+------------+
(0 rows)
See Also
Was this helpful?
