Cannot Rollback TRUNCATE In Redshift

Kevin Feasel

2018-10-30

Syntax

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.

Related Posts

Request: Add Support for Row Pattern Recognition

Itzik Ben-Gan would like to see Row Pattern Recognition make it into T-SQL: The ISO/IEC 9075:2016 standard (aka SQL:2016) introduces support for Row Pattern Recognition (RPR) in SQL. Similar to using regular expressions to identify patterns in a string, RPR allows you to use regular expressions to identify patterns in a sequence of rows. To […]

Read More

Auto-Escaping XML Characters

Emanuele Meazzo shows how you can auto-escape XML characters using T-SQL: Recently I had to look up the definition for a bunch of SQL objects and didn’t want to manually retrieve them manually in SSMS (with Create Scripts) or Visual Studio (by searching the object name in my TFS repository). Since lazyness and automation are […]

Read More

Categories

October 2018
MTWTFSS
« Sep Nov »
1234567
891011121314
15161718192021
22232425262728
293031