title: Efficiently duplicate some rows in PostgreSQL table
body: <p>I have PostgreSQL 9 database that uses auto-incrementing integers as primary keys. I want to duplicate some of the rows in a table (based on some filter criteria), while changing one or two values, i.e. copy all column values, except for the ID (which is auto-generated) and possibly another column.</p>
  
  <p>However, I also want to get the mapping from old to new IDs. Is there a better way to do it then just querying for the rows to copy first and then inserting new rows one at a time?</p>
  
  <p>Essentially I want to do something like this:</p>
  
  <pre><code>INSERT INTO my_table (col1, col2, col3)
  SELECT col1, 'new col2 value', col3
  FROM my_table old
  WHERE old.some_criteria = 'something'
  RETURNING old.id, id;
  </code></pre>
  
  <p>However, this fails with <code>ERROR:  missing FROM-clause entry for table "old"</code> and I can see why: Postgres must be doing the SELECT first and then inserting it and the <code>RETURNING</code> clauses only has access to the newly inserted row.</p>