On this page
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 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)