title: wrapping postgresql commands in a transaction: truncate vs delete or upsert/merge
body: <p>I am using the following commands below in postgresql 9.1.3 to move data from a temp staging table to a table being used in a webapp (geoserver) all in the same db. Then dropping the temp table.</p>
  
  <ol>
  <li><p>TRUNCATE table_foo;</p></li>
  <li><p>INSERT INTO table_foo</p>
  
  <p>SELECT * FROM table_temp;</p></li>
  <li><p>DROP TABLE table_temp;</p></li>
  </ol>
  
  <p>I want to wrap this in a transaction to allow for concurrency. The data-set is small less than 2000 rows and truncating is faster than delete. </p>
  
  <ul>
  <li>What is the best way to run these commands in a transaction?</li>
  <li>Is creating a function advisable or writing a UPSERT/MERGE etc in a CTE? </li>
  <li>Would it be better to DELETE all rows then bulk INSERT from temp table instead of TRUNCATE?</li>
  <li>In postgres which would allow for a roll back TRUNCATE or DELETE?</li>
  <li>The temp table is delivered daily via an ETL scripted in arcpy how could I automate the truncate/delete/bulk insert parts within postgres? </li>
  <li>I am open to using PL/pgsql, PL/python (or the recommended py for postgres)</li>
  </ul>
  
  <p>Currently I am manually executing the sql commands after the temp staging table is imported into my DB.  </p>