title: Postgres: upsert a row and update a primary key column
body: <p>Suppose I have two tables in my Postgres database:</p>
  
  <pre><code>create table transactions
  (
      id bigint primary key,
      doc_id bigint not null,
      -- lots of other columns...
      amount numeric not null
  );
  
  -- same columns
  create temporary table updated_transactions
  (
      id bigint primary key,
      doc_id bigint not null,
      -- lots of other columns...
      amount numeric not null
  );
  </code></pre>
  
  <p>Both tables have just a primary key, and no unique indexes.</p>
  
  <p>I need to upsert rows from <code>updated_transactions</code> into <code>transactions</code> using the following rules:</p>
  
  <ul>
  <li>id column values in <code>transactions</code> and <code>updated_transactions</code> don't match</li>
  <li>other columns like <code>doc_id</code>, etc (except of the <code>amount</code>) should match</li>
  <li>when a matching row is found, update both <code>amount</code> and <code>id</code> columns</li>
  <li>when a matching row is not found, insert it</li>
  </ul>
  
  <p><code>id</code> values in <code>updated_transactions</code> are taken from a sequence.
  A business object just populates <code>updated_transactions</code> and then merges the
  new or updated rows from it into <code>transactions</code> using an upsert query.
  So my old unchanged transactions keep their <code>id</code>s intact, and the updated ones
  are assigned new <code>id</code>s.</p>
  
  <p>In MSSQL and Oracle, it would be a <code>merge</code> statement similar to this:</p>
  
  <pre><code>merge into transactions t
  using updated_transactions ut on t.doc_id = ut.doc_id, ...
  when matched then
      update set t.id = ut.id, t.amount = ut.amount
  when not matched then
      insert (t.id, t.doc_id, ..., t.amount)
      values (ut.id, ut.doc_id, ..., ut.amount);
  </code></pre>
  
  <p>In PostgreSQL, I suppose it should be something like this:</p>
  
  <pre><code>insert into transactions(id, doc_id, ..., amount)
  select coalesce(t.id, ut.id), ut.doc_id, ... ut.amount
  from updated_transactions ut
  left join transactions t on t.doc_id = ut.doc_id, ....
      on conflict
      on constraint transactions_pkey
      do update
      set amount = excluded.amount, id = excluded.id
  </code></pre>
  
  <p>The problem is with the <code>do update</code> clause: <code>excluded.id</code> is an old value
  from <code>transactions</code> table, while I need a new value from <code>updated_transactions</code>.</p>
  
  <p><code>ut.id</code> value is inaccessible for the <code>do update</code> clause, and the only thing I can
  use is the <code>excluded</code> row. But the <code>excluded</code> row has only <code>coalesce(t.id, ut.id)</code>
  expression which returns old <code>id</code> values for the existing rows.</p>
  
  <p>Is it possible to update both <code>id</code> and <code>amount</code> columns using the upsert query?</p>