title: Postgres trigger interfering with 'on conflict do update set'
body: <p>I've got a SQL insert that should increment the <code>version</code> column and update the <code>time</code> column on a primary key conflict.</p>
  
  <pre><code>insert into items (pk, time, version, name)
          values (9999, now(), 1)
          on conflict (pk) do update set
            time = now(),
            version = items.version + 1,
            name = excluded.name
  </code></pre>
  
  <p>Everything works fine until I add a trigger to archive rows when an update happens. So if there's a row with version 1, and an insert happens later, the above query will increment the <code>version</code> column to be 2. But I want the old row with version 1 to go to the <code>archive</code> table.</p>
  
  <p>But when I add the trigger below, the above insert doesn't update the version and time columns as it did without this trigger. How can I fix the trigger? Is it because I'm doing a <code>return old</code>, or because it's a <code>before update</code> trigger?</p>
  
  <pre><code>CREATE FUNCTION archivefunction() RETURNS trigger
      LANGUAGE plpgsql
  AS $$
  begin
    insert into archive (pk, time, version, name)
      values (old.pk, old.time, old.version, old.name);
    return old;
    end
  $$
  
  CREATE TRIGGER archivetrigger
  BEFORE UPDATE ON items
  FOR EACH ROW EXECUTE PROCEDURE archivefunction()
  </code></pre>