Updated May 15, 2023
Introduction to Redshift truncate
Redshift truncate command is used to remove all the records from table, basically, we have using delete statement to delete data from table but for large table, we have using truncate command to delete whole data from table. Truncate command is deleting all the records from table also it is faster than the delete command, delete command deletes single as well as all records from table but truncate will removing all record from the table. Truncate is not used to remove a single record from table. After executing truncate command on table we cannot retrieve data.
Syntax:
Below is the syntax of truncate command in redshift are as follows.
- Truncate table_name;
- Truncate table table_name;
Parameter description syntax of truncate in redshift.
- Truncate – This is a command which was used in redshift to delete all the records from table. Using truncate it’s possible to delete all the records from the table.
- Table – This is an optional parameter that was used with truncate command in redshift. We can also execute truncate commands on table without using the table keywords.
- Table name – This is the name of table on which we have executed the truncate command. It’s a persistent or temporary table. At the time of truncating the table, we have no need to vacuum it again.
How does truncate work in Redshift?
- Truncate command in redshift will delete all the rows from the table without doing a full table scan.
- At the time of deleting records using the delete command, it will do a full table scan but the truncate command is not doing a full scan on the table at the time deleting records from a table.
- As compare to delete operation truncate is faster because the delete operation is doing a full table scan on the table.
- To execute the truncate command, we need the owner of the table or superuser privileges on the database. Another user cannot run the truncate command on the table.
- The below example shows that we need owner or superuser privileges to execute truncate command in redshift.
- In the below example, we have used the test users to execute the truncate commands. But we can see that it shows the error like it will must the owner of that particular table. We have to try to truncate the data table.
Code:
Truncate table date;
Figure – we need owner or superuser privileges to execute truncate command in redshift.
- If suppose we want to delete all records from the table and also if we have not required those records in future, then truncate command is more efficient than delete command in redshift.
- Truncate table statement is performing same operations as delete statement without where clause in redshift. If we have used where clause with a delete statement it will deleting a specified row.
- After truncating data from the table we have no need to run analyze or vacuum command on the table.
- Basically, we have not used truncate table statement on the table which contained the foreign key references from the other tables, because it will truncate all tables which have foreign key references.
- Truncate table statement is reclaiming the storage from table.
- Using the truncate command, we can delete a single table in a single command. We cannot delete multiple tables in a single command as we have to delete in other databases.
- Truncate table statement is not transaction-safe in redshift. Truncate table is not rollbacking the statement, if we have used the commit statement after the truncate table command.
- At the time of using the truncate command in redshift, we need to use it very carefully. Because it will not be any undo available after executing the truncate statement.
- At the time of using the truncate command in production, we need to use it very carefully.
- At the time of using the truncate command, it will automatically commit the transaction. We cannot roll back it after committing.
Code:
Truncate table venue;
Rollback;
Select * from venue;
Figure – At the time of using the truncate command it will automatically commit the transaction. We cannot roll back it after committing.
- In the above example, we can see that we cannot roll back the transaction after executing the truncate command.
- In the first example, we have truncated the table name as a venue, and in the second example, we have to try to roll back the transaction. But in the third example, we can see we cannot roll back the transaction, it will retrieve the empty result set.
- We can truncate any table in redshift also if it includes the reference of the foreign key constraints.
- In redshift truncate command will commit other transactions when it will commit its own transaction.
- Truncate statement in redshift will acquire access exclusive lock on the table on which we have performing truncate operations.
- After executing the truncate command all records from the table will delete, and it will appear empty for concurrent transactions which were running on the database server.
Examples
Below example shows truncate in redshift are as follows.
Truncate single table using truncate command
- The below example shows that truncate single table using truncate command are as follows. In the below example, we have truncated the date table using truncate command.
- After truncating the date table, we can see that table will return the empty result.
Code:
Truncate table date;
Select * from date;
Truncate single table without using table keyword –
- The below example shows that truncate a single table without using table keywords are as follows. In the below example we have truncated the sales table using truncate command.
- We have not used the table keywords after the truncate command.
- After truncating the sales table we can see that table will return the empty result set.
Code:
Truncate sales;
Select * from sales;
Truncate multiple table using truncate command
- The below example shows that truncate multiple table using truncate command are as follows. In the below example, we have truncated the users and event table by using truncate command.
- We have truncated the users and event table in two commands. We cannot delete multiple tables in a single command.
Code:
Truncate table users;
Truncate table event;
Conclusion
The truncate command is used to delete all the records in redshift. After truncating the records we cannot roll back them. After running the truncate command on the table we have no need to run analyze or vacuum on the table, truncate will automatically reclaim the space from the table.
Recommended Articles
This is a guide to Redshift truncate. Here we discuss How does truncate works in Redshift along with the examples in detail. You may also have a look at the following articles to learn more –
