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.