title: How to copy values from table A to B and inserting reference to B in A?
body: <p>I would like to copy values from <code>a_table.foo</code> to <code>b_table.bar</code> and insert the generated <code>b_table.id</code> to <code>a_table.b_id</code>:</p>
  
  <pre><code>WITH b_inserts AS (
    INSERT INTO b_table (bar)
    SELECT foo
    FROM a_table
    RETURNING id as b_id, a_table.id as a_id
  )
  
  UPDATE a_table
  SET b_id = b_inserts.b_id
  FROM b_inserts
  WHERE a.id = b_inserts.a_id
  </code></pre>
  
  <p><a href="http://sqlfiddle.com/#!17/13819/1" rel="nofollow noreferrer">http://sqlfiddle.com/#!17/13819/1</a></p>
  
  <p>It fails because I cannot return <code>a_table.id</code> from <code>b_inserts</code> as it's not part of the inserted row which is all <code>RETURNING</code> can access.</p>
  
  <p>Is there a way to solve this without having to create a temporary <code>b_table.a_id</code> column?</p>