How To Truncate Table in Redshift

Sometimes you may need to empty table content in Redshift, or delete all rows in a table. It is also known as table truncation. In this article, we will look at how to truncate table in Redshift.


How To Truncate Table in Redshift

Here are the steps to truncate table in Redshift using TRUNCATE TABLE command. Please be very careful when you use this command, as there is no way to undo its result.

Here is the syntax of TRUNCATE TABLE command

TRUNCATE TABLE table_name

You just need to specify table name that you want to truncate.

Also read : How to Drop Table in Redshift


Let us say you have a table sales(id, sale_date, amount)

# create table sales(id int, sale_date date, amount int);

# insert into sales(id, sale_date, amount)
  values(1,'2021-01-01',100),
        (2,'2021-01-02',200);

# select * from sales;
 +------+------------+--------+
 | id   | sale_date  | amount |
 +------+------------+--------+
 |    1 | 2021-01-01 |    100 |
 |    2 | 2021-01-02 |    200 |
 +------+------------+--------+

Also read : How to Copy Table in Redshift


Here is the command to truncate sales table in Redshift.

# truncate table sales;

# select * from sales;
 Empty set (0.00 sec)

As you can see, the table content has been emptied.

Please note, if your table has auto increment column, its value will be reset after TRUNCATE TABLE statement.

Need a reporting tool for Redshift? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

mm

About Ubiq

Ubiq is a dashboard & reporting platform that makes it easy to get insights from data. Build dashboards, charts & reports for your business in minutes. Try it for free today!