title: How do I write a transaction for this case in AWS Redshift?
body: <p>I'll describe the scenario first, as I might be overcomplicating the issue and if so, I'll just delete the question.</p>
  
  <p>I am running a script that re-creates a table once a day. Due to the nature of the creation, the existing table has to be dropped and then the new table created. The process is basically like so:</p>
  
  <ul>
  <li>Execute the <code>CREATE TABLE AS</code> query and give table a placeholder name.</li>
  <li><code>DROP TABLE IF EXISTS</code> the existing table.</li>
  <li><code>ALTER TABLE RENAME</code> the newly created table.</li>
  </ul>
  
  <p>However, part of the requirement is that if there are errors in dropping or renaming, I should be able to rollback the drop and still retain the newly-created "temporary" table. Obviously, I have to have a transaction.</p>
  
  <p>How do I write the complete script, though? I was thinking something like...</p>
  
  <pre><code>CREATE TABLE target_temp AS SELECT * FROM src;
  BEGIN;
  DROP TABLE IF EXISTS target;
  ALTER TABLE target_temp RENAME TO target
  COMMIT;
  </code></pre>
  
  <p>Or should I move <code>BEGIN;</code> to before the <code>CREATE TABLE</code>? If I understand correctly, by doing that, even the create command will be rolled back if there are errors in the drop/rename, right? Because this is not what is required.</p>