TRUNCATE

The TRUNCATE statement removes all rows from a table. At a high level, it works by dropping the table and recreating a new table with the same name.

The TRUNCATE statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

TRUNCATE is a schema change, and as such is not transactional. For more information about how schema changes work, see Online Schema Changes.

Synopsis

Required privileges

The user must have the DROP privilege on the table.

Parameters

Parameter	Description
`table_name`	The 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.

Viewing schema changes

This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.

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`

> 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)

Statements

Syntax

Data Types

Spatial Data

Constraints

Synopsis

Required privileges

Parameters

Viewing schema changes

Examples

Truncate a table (no foreign key dependencies)

Truncate a table and dependent tables

Truncate dependent tables using `CASCADE`

Truncate dependent tables explicitly

See also

​Synopsis

​Required privileges

​Parameters

​Viewing schema changes

​Examples

​Truncate a table (no foreign key dependencies)

​Truncate a table and dependent tables

​Truncate dependent tables using CASCADE

​Truncate dependent tables explicitly

​See also

Synopsis

Required privileges

Parameters

Viewing schema changes

Examples

Truncate a table (no foreign key dependencies)

Truncate a table and dependent tables

Truncate dependent tables using `CASCADE`

Truncate dependent tables explicitly

See also