title: Column Copy and Update vs. Column Create and Insert
body: <p>I have a table with 32 Million rows and 31 columns in PostgreSQL 9.2.10. I am altering the table by adding columns with updated values.</p>
  
  <p>For example, if the initial table is:</p>
  
  <pre><code>id     initial_color
  --     -------------
  1      blue
  2      red
  3      yellow
  </code></pre>
  
  <p>I am modifying the table so that the result is:</p>
  
  <pre><code>id     initial_color     modified_color
  --     -------------     --------------
  1      blue              blue_green
  2      red               red_orange
  3      yellow            yellow_brown
  </code></pre>
  
  <p>I have code that will read the initial_color column and update the value.</p>
  
  <p>Given that my table has 32 million rows and that I have to apply this procedure on five of the 31 columns, what is the most efficient way to do this? My present choices are: </p>
  
  <ol>
  <li>Copy the column and update the rows in the new column</li>
  <li>Create an empty column and insert new values</li>
  </ol>
  
  <p>I could do either option with one column at a time or with all five at once.  The columns types are either <code>character varying</code> or <code>character</code>.</p>