title: How to create *_swap tables (w/ indexes) in postgresql
body: <p>I have a <code>students</code> table in postgres that is populated via an external source. Each night we populate the <code>students_swap</code> table, and then after the long running operation is complete we rename it to <code>students</code> and the original table then becomes <code>students_swap</code> to be used the next day. </p>
  
  <p>The problem with this is that when we add a new column or index to the original table we must remember to also do so on the swap table. I am attempting to automate some of this w/ the following:</p>
  
  <pre><code>-- Drop the swap table if it's already there...
  DROP TABLE IF EXISTS students_swap;
  
  -- Recreate the swap table using the original as a template...
  CREATE TABLE students_swap AS SELECT * FROM students WHERE 1=2;
  
  ... populate the swap table ....
  
  ALTER TABLE students RENAME TO students_temp;
  ALTER TABLE students_swap RENAME TO ps_students;
  ALTER TABLE students_temp RENAME TO students_swap;
  </code></pre>
  
  <p>This works well for creating the table structure but no indices are created for the swap table. </p>
  
  <p>My question is how do I copy all of the indexes in addition to the table structure to make sure my original table and swap table stay in sync?</p>