TRUNCATE and DROP: Emptying and Removing Tables

Two commands remove data in bulk. TRUNCATE keeps the table, and DROP TABLE removes the table completely. For the examples below, a products table starts with three rows:

SELECT * FROM products;

Output:

 id | name     | price
----+----------+--------
  1 |   Laptop | 999.99
  2 |    Mouse |  19.99
  3 | Keyboard |  49.99

(3 rows)

TRUNCATE: remove all rows, keep the table

TRUNCATE products;

SELECT * FROM products;

Output:

 id | name | price
----+------+-------


(0 rows)

All rows are deleted. The table and its columns are still there, ready for new rows.

DROP TABLE: remove the table itself

DROP TABLE products;

Now try to read the table:

SELECT * FROM products;

Output:

InvalidRequest: Error from server: code=2200 [Invalid query] message="table products does not exist"

The table, its columns and all its rows are gone. So the SELECT now fails, because there is no products table anymore.

Dropping a table that does not exist is also an error. If you are not sure that a table exists, add IF EXISTS. The products table is gone now, but this runs without an error:

DROP TABLE IF EXISTS products;

Which one should you use?

You want to...Use
Remove all rows and keep the tableTRUNCATE
Remove a table you no longer needDROP TABLE
Remove only some rowsDELETE (in the Modify Data section)

To empty a table, use TRUNCATE instead of deleting rows one by one. In Cassandra, each deleted row leaves a marker behind, called a tombstone, and many tombstones make reads slower. TRUNCATE removes all the data at once.

The products table in the exercise is full of test data. Remove all its rows, but keep the table.