Press "Enter" to skip to content

Cannot Rollback TRUNCATE In Redshift

Derik Hammer notes that you cannot rollback a TRUNCATE TABLE operation in Redshift:

In SQL Server, or PostgreSQL for that matter, the TRUNCATE command is allowed in a transaction and it will commit or rollback like any other DML operation. In all of the scripts, below, I will do the following.

  1. Check my row counts

  2. Begin a transaction

  3. TRUNCATE the table

  4. INSERT one row

  5. Check my row counts from within the transaction

  6. ROLLBACK

  7. Check to see if  my row checks revert or not

That’s an important behavioral difference when loading data using a truncate-and-reload pattern.