Skip to content

Latest commit

 

History

History
160 lines (122 loc) · 3.67 KB

truncate.md

File metadata and controls

160 lines (122 loc) · 3.67 KB
title summary toc
TRUNCATE
The TRUNCATE statement deletes all rows from specified tables.
true

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.

{% include {{{ page.version.version }}/misc/schema-change-stmt-note.md %}

Synopsis

{% include {{ page.version.version }}/sql/diagrams/truncate.html %}

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.

Limitations

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

Viewing schema changes

{% include {{ page.version.version }}/misc/schema-change-view-job.md %}

Examples

Truncate a table (no foreign key dependencies)

{% include copy-clipboard.html %}

> SELECT * FROM t1;
+----+------+
| id | name |
+----+------+
|  1 | foo  |
|  2 | bar  |
+----+------+
(2 rows)

{% include copy-clipboard.html %}

> TRUNCATE t1;

{% include copy-clipboard.html %}

> 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

{{site.data.alerts.callout_danger}}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.{{site.data.alerts.end}}

{% include copy-clipboard.html %}

> TRUNCATE customers;
pq: "customers" is referenced by foreign key from table "orders"

{% include copy-clipboard.html %}

> TRUNCATE customers CASCADE;

{% include copy-clipboard.html %}

> SELECT * FROM customers;
+----+-------+
| id | email |
+----+-------+
+----+-------+
(0 rows)

{% include copy-clipboard.html %}

> SELECT * FROM orders;
+----+----------+------------+
| id | customer | orderTotal |
+----+----------+------------+
+----+----------+------------+
(0 rows)

Truncate dependent tables explicitly

{% include copy-clipboard.html %}

> TRUNCATE customers, orders;

{% include copy-clipboard.html %}

> SELECT * FROM customers;
+----+-------+
| id | email |
+----+-------+
+----+-------+
(0 rows)

{% include copy-clipboard.html %}

> SELECT * FROM orders;
+----+----------+------------+
| id | customer | orderTotal |
+----+----------+------------+
+----+----------+------------+
(0 rows)

See also